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
On Sat, Dec 20, 2003 at 11:42:27AM +0530, A.Bhuvaneswaran wrote:
>
> > 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
whereorder_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
whereorder_date >= '01/04/2003';
> 2003-12-20 11:22:04 [1558] LOG: duration: 623.982111 sec
> </log>
----------------------------------------------------------------------
Ray Ontko rayo@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/