Обсуждение: Index is not used

Поиск
Список
Период
Сортировка

Index is not used

От
"A.Bhuvaneswaran"
Дата:
Hi,

I am using pgsql 7.3.4 on rh7.3.

I have a table and an index on it. The index is used when i use '='
operator for the indexed field in the where condition. But the index is
not used when i use '>=' operator for the same field. Herewith i have
attached the explain output. Hope to get some lights.

regards,
bhuvaneswaran

Вложения

Re: Index is not used

От
Tom Lane
Дата:
"A.Bhuvaneswaran" <bhuvan@symonds.net> writes:
> 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

Re: Index is not used

От
"A.Bhuvaneswaran"
Дата:
> 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


Вложения

Re: Index is not used

От
Ray Ontko
Дата:
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/

Re: Index is not used

От
Tom Lane
Дата:
Ray Ontko <rayo@ontko.com> writes:
> BTW, Are you sure that you need to do the "distinct"?

That's a good question to ask.

> 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