Please use "explain analyze" instead of just "explain"; the additional
time.
table. The cluster command (or create table as select from order by)
can be used to put a table in physical order.
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
order. (Anyone know if this will actually help?)
Post by Tom LaneUnsurprising. 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 & Co. Software Consulting Services http://www.ontko.com/