PostgreSQL Index Optimization

Question

currently I have the following indeces for the below SQL Select statement. Nevertheless the query seems still slow to me (10.000 records). Do you have any recommendations?

1. index category_id
2. index delivery_date
3. index on product_id, product_name

Here's my DDL:

Create table product (    
  product_id serial,
  category_id int2,
  product_name varchar(50),
  delivery_date timestamp,
  subtitle varchar(20),
  price numeric(10,2),
  retail_price numeric(10,2),
  language_id int2,
  store_id int2,
  reseller_id int2    
);

and SQL:

Select * 
from product 
WHERE delivery_date > '2012-10-20 06:00:00' AND category_id = 1 
ORDER BY product_id, product_name;

Any help would be appreciated.

Below the output of EXPLAIN ANALYZE:

Sort  (cost=18.11..18.12 rows=1 width=119) (actual time=0.064..0.064 rows=0 loops=1)
Sort Key: product_id, product_name
Sort Method: quicksort  Memory: 25kB
  ->  Seq Scan on product  (cost=0.00..18.10 rows=1 width=119) (actual time=0.019..0.019 rows=0 loops=1)
Filter: ((delivery_date > '2012-10-20 06:00:00'::timestamp without time zone) AND (category_id = 1))
Total runtime: 0.098 ms

Answer 1

Absolutely ideal configuration for your query would be to have compound index for (delivery_date, category_id, product_id) or (category_id, delivery_date, product_id).

In practice, having index for just (category_id, product_id) may be enough to get acceptable performance.

At any rate, EXPLAIN ANALYZE <original_query> is your best friend.

One more note: in your query, ORDER BY product_id, product_name is always going to get the same result as simply ORDER BY product_id.