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?
- index category_id
- index delivery_date
- 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
explain analyze <your select statement>
. – Mike Sherrill 'Cat Recall' Oct 20 '12 at 15:22EXPLAIN (BUFFERS, ANALYZE)
output, etc. – Craig Ringer Oct 20 '12 at 16:09