Discussion:
Index is not used
(too old to reply)
Tom Lane
2003-12-20 04:39:03 UTC
Permalink
I have a table and an index on it. The index is used when i use '=3D'
operator for the indexed field in the where condition. But the index is
not used when i use '>=3D' operator for the same field.
Unsurprising. An inequality condition may require fetching many rows
(the planner is estimating 336289 rows here...) and so an indexscan is
not necessarily quicker. Have you compared actual runtimes with
enable_seqscan on and off?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org
Ray Ontko
2003-12-20 14:47:52 UTC
Permalink
Please use "explain analyze" instead of just "explain"; the additional
information is very helpful to see where the query is actually spending
time.

Have you considered clustering on the order_date index? We have
seen dramatic improvements in situations where the table is
physically ordered by the index most commonly used to access the
table. The cluster command (or create table as select from order by)
can be used to put a table in physical order.

BTW, Are you sure that you need to do the "distinct"? If you do,
then you might try putting the order_date column first in the
select list. The distinct is requiring that all the rows be sorted
so that they can be uniqified. I'm hoping that by putting order_date
first in the that the sort will have to do less work to put them in
order. (Anyone know if this will actually help?)

Ray
Post by Tom Lane
Unsurprising. An inequality condition may require fetching many rows
(the planner is estimating 336289 rows here...) and so an indexscan is
not necessarily quicker. Have you compared actual runtimes with
enable_seqscan on and off?
I did run with enable_seqscan off. You are right, the plan shows that
indexscan is not quicker. Here is the explain output.
On the other hand, i have calculated the actual runtime with
enable_seqscan on and off. The runtime is 617 secs & 623 secs
respectively. I have also attached the log details. Is there any way to
increase this speed?
regards,
bhuvaneswaran
<explain>
=> set enable_indexscan = on;
SET
=> set enable_seqscan = off;
SET
=> EXPLAIN select distinct comp_code, supp_code, supp_name, buyer_code, buyer_name, item_code, revision, description, po_no, pos_no, order_date, delivery_date, reschedule_date, reschedule_qty, ordered_qty, delivered_qty, backorder_qty, stock, warehouse_code, allocated, exception, run_date from reschedule_bak where order_date = '01/04/2003';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6999.59..7123.48 rows=215 width=213)
-> Sort (cost=6999.59..7004.98 rows=2155 width=213)
Sort Key: comp_code, supp_code, supp_name, buyer_code, buyer_name, item_code, revision, description, po_no, pos_no, order_date, delivery_date, reschedule_date, reschedule_qty, ordered_qty, delivered_qty, backorder_qty, stock, warehouse_code, allocated, exception, run_date
-> Index Scan using reschedule_bak_order_date_idx on reschedule_bak (cost=0.00..6880.30 rows=2155 width=213)
Index Cond: (order_date = '01/04/2003'::date)
(5 rows)
=> EXPLAIN select distinct comp_code, supp_code, supp_name, buyer_code, buyer_name, item_code, revision, description, po_no, pos_no, order_date, delivery_date, reschedule_date, reschedule_qty, ordered_qty, delivered_qty, backorder_qty, stock, warehouse_code, allocated, exception, run_date from reschedule_bak where order_date >= '01/04/2003';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=659460.84..678797.48 rows=33629 width=213)
-> Sort (cost=659460.84..660301.57 rows=336289 width=213)
Sort Key: comp_code, supp_code, supp_name, buyer_code, buyer_name, item_code, revision, description, po_no, pos_no, order_date, delivery_date, reschedule_date, reschedule_qty, ordered_qty, delivered_qty, backorder_qty, stock, warehouse_code, allocated, exception, run_date
-> Index Scan using reschedule_bak_order_date_idx on reschedule_bak (cost=0.00..587396.88 rows=336289 width=213)
Index Cond: (order_date >= '01/04/2003'::date)
(5 rows)
=>\q
</explain>
<log>
2003-12-20 10:35:10 [1558] LOG: query: set enable_seqscan = on;
2003-12-20 10:35:10 [1558] LOG: duration: 0.000458 sec
2003-12-20 10:36:57 [1558] LOG: query: select distinct comp_code, supp_code, supp_name, buyer_code, buyer_name, item_code, revision, description, po_no, pos_no, order_date, delivery_date, reschedule_date, reschedule_qty, ordered_qty, delivered_qty, backorder_qty, stock, warehouse_code, allocated, exception, run_date from reschedule_bak where order_date >= '01/04/2003';
2003-12-20 10:47:15 [1558] LOG: duration: 617.886026 sec
2003-12-20 11:11:37 [1558] LOG: query: set enable_seqscan = off;
2003-12-20 11:11:37 [1558] LOG: duration: 0.000458 sec
2003-12-20 11:11:40 [1558] LOG: query: select distinct comp_code, supp_code, supp_name, buyer_code, buyer_name, item_code, revision, description, po_no, pos_no, order_date, delivery_date, reschedule_date, reschedule_qty, ordered_qty, delivered_qty, backorder_qty, stock, warehouse_code, allocated, exception, run_date from reschedule_bak where order_date >= '01/04/2003';
2003-12-20 11:22:04 [1558] LOG: duration: 623.982111 sec
</log>
----------------------------------------------------------------------
Ray Ontko ***@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Tom Lane
2003-12-20 17:01:18 UTC
Permalink
Post by Ray Ontko
BTW, Are you sure that you need to do the "distinct"?
That's a good question to ask.
Post by Ray Ontko
If you do,
then you might try putting the order_date column first in the
select list. The distinct is requiring that all the rows be sorted
so that they can be uniqified. I'm hoping that by putting order_date
first in the that the sort will have to do less work to put them in
order. (Anyone know if this will actually help?)
I don't believe it will help much if at all. Increasing sort_mem might
help, though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

Loading...