pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlimit 1 and functional indexes
6+ messages / 3 participants
[nested] [flat]
* limit 1 and functional indexes
@ 2004-01-28 11:23 Alexandra Birch <alexandra@trymedia.com>
2004-01-29 12:52 ` Re: limit 1 and functional indexes Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Alexandra Birch @ 2004-01-28 11:23 UTC (permalink / raw)
To: pgsql-sql
Hi,
Postgres choses the wrong index when I add limit 1 to the query.
This should not affect the index chosen.
I read that functional indexes are sometimes not chosen correctly by
optimizer.
Is there anything I can do to always use the functional index in the
following queries?
Query with limit 1 choses wrong index:
---------------------------------------------------------------------------------------
explain
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
order by order_date DESC LIMIT 1
Index Scan Backward using transactions_date_aff on transactions (cost=0.00..930780.96 rows=2879 width=33)
---------------------------------------------------------------------------------------
Without limit 1 choses correct index:
---------------------------------------------------------------------------------------
explain
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
order by order_date DESC
Index Scan using transactions_pop_i on transactions (cost=0.00..11351.72 rows=2879 width=33)
---------------------------------------------------------------------------------------
We have postgresql-7.3.2-3.
Thank you,
Alexandra
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: limit 1 and functional indexes
2004-01-28 11:23 limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
@ 2004-01-29 12:52 ` Bruno Wolff III <bruno@wolff.to>
2004-01-29 15:02 ` Re: limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2004-01-29 12:52 UTC (permalink / raw)
To: Alexandra Birch <alexandra@trymedia.com>; +Cc: pgsql-sql
On Wed, Jan 28, 2004 at 12:23:38 +0100,
Alexandra Birch <alexandra@trymedia.com> wrote:
> Hi,
>
> Postgres choses the wrong index when I add limit 1 to the query.
> This should not affect the index chosen.
I don't know the complete answer to your question, but since no one else
has commented I will answer what I can.
It IS reasobable for the planner to choose a different plan when you
add a LIMIT clause to a query.
> I read that functional indexes are sometimes not chosen correctly by
> optimizer.
I don't believe there are any particular problems with functional indexes.
The opitmizer isn't perfect and will sometimes choose poor plans.
> Is there anything I can do to always use the functional index in the
> following queries?
Have you done an ANALYZE of the table recently?
It might be useful to see the EXPLAIN ANALYZE output, rather than just
the EXPLAIN output, as that will give the actual times needed to do
the various steps.
>
> Query with limit 1 choses wrong index:
> ---------------------------------------------------------------------------------------
> explain
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
> order by order_date DESC LIMIT 1
>
> Index Scan Backward using transactions_date_aff on transactions (cost=0.00..930780.96 rows=2879 width=33)
> ---------------------------------------------------------------------------------------
>
> Without limit 1 choses correct index:
> ---------------------------------------------------------------------------------------
> explain
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
> order by order_date DESC
>
> Index Scan using transactions_pop_i on transactions (cost=0.00..11351.72 rows=2879 width=33)
> ---------------------------------------------------------------------------------------
>
> We have postgresql-7.3.2-3.
> Thank you,
>
> Alexandra
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: limit 1 and functional indexes
2004-01-28 11:23 limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
2004-01-29 12:52 ` Re: limit 1 and functional indexes Bruno Wolff III <bruno@wolff.to>
@ 2004-01-29 15:02 ` Alexandra Birch <alexandra@trymedia.com>
2004-01-29 15:11 ` Re: limit 1 and functional indexes Bruno Wolff III <bruno@wolff.to>
2004-01-29 15:43 ` Re: [SQL] limit 1 and functional indexes Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 6+ messages in thread
From: Alexandra Birch @ 2004-01-29 15:02 UTC (permalink / raw)
To: bruno@wolff.to; +Cc: pgsql-sql
> >
> > Postgres choses the wrong index when I add limit 1 to the query.
> > This should not affect the index chosen.
>
> I don't know the complete answer to your question, but since no one else
> has commented I will answer what I can.
Thanks - your reply is apreciated :)
> It IS reasobable for the planner to choose a different plan when you
> add a LIMIT clause to a query.
OK - I'll investigate this further.
> > I read that functional indexes are sometimes not chosen correctly by
> > optimizer.
>
> I don't believe there are any particular problems with functional indexes.
> The opitmizer isn't perfect and will sometimes choose poor plans.
OK - but there was some discussion about statistics for functional indexes, for eg:
http://archives.postgresql.org/pgsql-general/2004-01/msg00978.php
This does not help me solve my problem though :)
> > Is there anything I can do to always use the functional index in the
> > following queries?
>
> Have you done an ANALYZE of the table recently?
Yip - I should have said we do a daily VACUUM ANALYZE.
> It might be useful to see the EXPLAIN ANALYZE output, rather than just
> the EXPLAIN output, as that will give the actual times needed to do
> the various steps.
I thought the cost values would be enough from the EXPLAIN alone.
And the query takes so long to run :(
Here is the output of EXPLAIN ANALYZE first with limit 1 then without:
explain analyze
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
order by order_date DESC LIMIT 1;
--------------------------------------------------------------------------------------------------
Limit (cost=0.00..332.44 rows=1 width=33) (actual time=377745.75..377745.75 rows=0 loops=1)
-> Index Scan Backward using transactions_date_aff on transactions (cost=0.00..982549.96 rows=2956 width=33) (actual
time=377718.61..377718.61 rows=0 loops=1)
Filter: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
Total runtime: 378439.32 msec
explain analyze
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
order by order_date DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
-------------
Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17 rows=0 loops=1)
Sort Key: order_date
-> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.79 rows=2956 width=33) (actual time=126.13..126.13
rows=0 loops=1)
Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
Total runtime: 248.25 msec
Thank you,
Alexandra
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: limit 1 and functional indexes
2004-01-28 11:23 limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
2004-01-29 12:52 ` Re: limit 1 and functional indexes Bruno Wolff III <bruno@wolff.to>
2004-01-29 15:02 ` Re: limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
@ 2004-01-29 15:11 ` Bruno Wolff III <bruno@wolff.to>
2004-01-30 06:07 ` Re: limit 1 and functional indexes Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2004-01-29 15:11 UTC (permalink / raw)
To: Alexandra Birch <alexandra@trymedia.com>; +Cc: pgsql-sql
On Thu, Jan 29, 2004 at 16:02:06 +0100,
Alexandra Birch <alexandra@trymedia.com> wrote:
>
> Here is the output of EXPLAIN ANALYZE first with limit 1 then without:
The time estimate for the limit 1 case is way off. I can't tell if that
is a bug or not having detailed enough statistics.
Hopefully someone more knowlegable will take a look at this question.
>
> explain analyze
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
> order by order_date DESC LIMIT 1;
> --------------------------------------------------------------------------------------------------
> Limit (cost=0.00..332.44 rows=1 width=33) (actual time=377745.75..377745.75 rows=0 loops=1)
> -> Index Scan Backward using transactions_date_aff on transactions (cost=0.00..982549.96 rows=2956 width=33) (actual
> time=377718.61..377718.61 rows=0 loops=1)
> Filter: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
> Total runtime: 378439.32 msec
>
> explain analyze
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
> order by order_date DESC;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------
> -------------
> Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17 rows=0 loops=1)
> Sort Key: order_date
> -> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.79 rows=2956 width=33) (actual time=126.13..126.13
> rows=0 loops=1)
> Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
> Total runtime: 248.25 msec
>
> Thank you,
>
> Alexandra
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: limit 1 and functional indexes
2004-01-28 11:23 limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
2004-01-29 12:52 ` Re: limit 1 and functional indexes Bruno Wolff III <bruno@wolff.to>
2004-01-29 15:02 ` Re: limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
2004-01-29 15:11 ` Re: limit 1 and functional indexes Bruno Wolff III <bruno@wolff.to>
@ 2004-01-30 06:07 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 6+ messages in thread
From: Greg Stark @ 2004-01-30 06:07 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Alexandra Birch <alexandra@trymedia.com>; pgsql-sql
Bruno Wolff III <bruno@wolff.to> writes:
> > QUERY PLAN
> > ------------------------------------------------------------------------------------------------------------------------------------
> > Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17 rows=0 loops=1)
> > Sort Key: order_date
> > -> Index Scan using transactions_pop_i on transactions
> > (cost=0.00..11653.79 rows=2956 width=33)
> > (actual time=126.13..126.13 rows=0 loops=1)
> > Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
> > Total runtime: 248.25 msec
Yeah, the problem with functional indexes is that the optimizer doesn't have
any clue how the records are distributed since it only has statistics for
columns, not your expression. Notice it's estimating 2956 rows where in fact
there are 0.
I think someone was actually working on this so it may be improved in 7.5 but
I'm not sure.
Given the type of data you're storing, which looks like hex strings, are you
sure you need to do a case-insensitive search here? Can't you just uppercase
it when you store it?
The other option would be to use a subquery and force the planner not to pull
it up, something like:
select code
from (
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
offset 0
)
order by order_date DESC;
The offset 0 prevents the optimizer from pulling the subquery into the outer
query. I think this will prevent it from even considering the order_date index
scan, but you'll have to try to be sure.
--
greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] limit 1 and functional indexes
2004-01-28 11:23 limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
2004-01-29 12:52 ` Re: limit 1 and functional indexes Bruno Wolff III <bruno@wolff.to>
2004-01-29 15:02 ` Re: limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
@ 2004-01-29 15:43 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2004-01-29 15:43 UTC (permalink / raw)
To: Alexandra Birch <alexandra@trymedia.com>; +Cc: pgsql-performance@postgresql.org
One other suggestion I forgot is that this should move over to the
performance list rather than being on the sql list. The right people
are more likely to see your question there.
On Thu, Jan 29, 2004 at 16:02:06 +0100,
Alexandra Birch <alexandra@trymedia.com> wrote:
>
> > >
> > > Postgres choses the wrong index when I add limit 1 to the query.
> > > This should not affect the index chosen.
> >
> > I don't know the complete answer to your question, but since no one else
> > has commented I will answer what I can.
>
> Thanks - your reply is apreciated :)
>
> > It IS reasobable for the planner to choose a different plan when you
> > add a LIMIT clause to a query.
>
> OK - I'll investigate this further.
>
> > > I read that functional indexes are sometimes not chosen correctly by
> > > optimizer.
> >
> > I don't believe there are any particular problems with functional indexes.
> > The opitmizer isn't perfect and will sometimes choose poor plans.
>
> OK - but there was some discussion about statistics for functional indexes, for eg:
> http://archives.postgresql.org/pgsql-general/2004-01/msg00978.php
> This does not help me solve my problem though :)
>
> > > Is there anything I can do to always use the functional index in the
> > > following queries?
> >
> > Have you done an ANALYZE of the table recently?
>
> Yip - I should have said we do a daily VACUUM ANALYZE.
>
> > It might be useful to see the EXPLAIN ANALYZE output, rather than just
> > the EXPLAIN output, as that will give the actual times needed to do
> > the various steps.
>
> I thought the cost values would be enough from the EXPLAIN alone.
> And the query takes so long to run :(
>
> Here is the output of EXPLAIN ANALYZE first with limit 1 then without:
>
> explain analyze
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
> order by order_date DESC LIMIT 1;
> --------------------------------------------------------------------------------------------------
> Limit (cost=0.00..332.44 rows=1 width=33) (actual time=377745.75..377745.75 rows=0 loops=1)
> -> Index Scan Backward using transactions_date_aff on transactions (cost=0.00..982549.96 rows=2956 width=33) (actual
> time=377718.61..377718.61 rows=0 loops=1)
> Filter: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
> Total runtime: 378439.32 msec
>
> explain analyze
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
> order by order_date DESC;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------
> -------------
> Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17 rows=0 loops=1)
> Sort Key: order_date
> -> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.79 rows=2956 width=33) (actual time=126.13..126.13
> rows=0 loops=1)
> Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text)
> Total runtime: 248.25 msec
>
> Thank you,
>
> Alexandra
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-01-30 06:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-28 11:23 limit 1 and functional indexes Alexandra Birch <alexandra@trymedia.com>
2004-01-29 12:52 ` Bruno Wolff III <bruno@wolff.to>
2004-01-29 15:02 ` Alexandra Birch <alexandra@trymedia.com>
2004-01-29 15:11 ` Bruno Wolff III <bruno@wolff.to>
2004-01-30 06:07 ` Greg Stark <gsstark@mit.edu>
2004-01-29 15:43 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox