> 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>
signature.asc
Description: This is a digitally signed message part
