pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT ignoring index even though ORDER BY and LIMIT present
7+ messages / 6 participants
[nested] [flat]
* SELECT ignoring index even though ORDER BY and LIMIT present
@ 2010-06-02 20:28 Jori Jovanovich <jori@dimensiology.com>
0 siblings, 4 replies; 7+ messages in thread
From: Jori Jovanovich @ 2010-06-02 20:28 UTC (permalink / raw)
To: pgsql-performance
hi,
I have a problem space where the main goal is to search backward in time for
events. Time can go back very far into the past, and so the
table can get quite large. However, the vast majority of queries are all
satisfied by relatively recent data. I have an index on the row creation
date and I would like almost all of my queries to have a query plan looking
something like:
Limit ...
-> Index Scan Backward using server_timestamp_idx on events
(cost=0.00..623055.91 rows=8695 width=177)
...
However, PostgreSQL frequently tries to do a full table scan. Often what
controls whether a scan is performed or not is dependent on the size of the
LIMIT and how detailed the WHERE clause is. In practice, the scan is always
the wrong answer for my use cases (where "always" is defined to be >99.9%).
Some examples:
(1) A sample query that devolves to a full table scan
EXPLAIN
SELECT events.id, events.client_duration, events.message,
events.created_by, events.source, events.type, events.event,
events.environment,
events.server_timestamp, events.session_id, events.reference,
events.client_uuid
FROM events
WHERE client_uuid ~* E'^foo bar so what'
ORDER BY server_timestamp DESC
LIMIT 20;
QUERY PLAN (BAD!)
--------------------------------------------------------------------------
Limit (cost=363278.56..363278.61 rows=20 width=177)
-> Sort (cost=363278.56..363278.62 rows=24 width=177)
Sort Key: server_timestamp
-> Seq Scan on events (cost=0.00..363278.01 rows=24 width=177)
Filter: (client_uuid ~* '^foo bar so what'::text)
(2) Making the query faster by making the string match LESS specific (odd,
seems like it should be MORE)
EXPLAIN
SELECT events.id, events.client_duration, events.message,
events.created_by, events.source, events.type, events.event,
events.environment,
events.server_timestamp, events.session_id, events.reference,
events.client_uuid
FROM events
WHERE client_uuid ~* E'^foo'
ORDER BY server_timestamp DESC
LIMIT 20;
QUERY PLAN (GOOD!)
------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1433.14 rows=20 width=177)
-> Index Scan Backward using server_timestamp_idx on events
(cost=0.00..623055.91 rows=8695 width=177)
Filter: (client_uuid ~* '^foo'::text)
(3) Alternatively making the query faster by using a smaller limit
EXPLAIN
SELECT events.id, events.client_duration, events.message,
events.created_by, events.source, events.type, events.event,
events.environment,
events.server_timestamp, events.session_id, events.reference,
events.client_uuid
FROM events
WHERE client_uuid ~* E'^foo bar so what'
ORDER BY server_timestamp DESC
LIMIT 10;
QUERY PLAN (GOOD!)
----------------------------------------------------------------------------------------------------------
Limit (cost=0.00..259606.63 rows=10 width=177)
-> Index Scan Backward using server_timestamp_idx on events
(cost=0.00..623055.91 rows=24 width=177)
Filter: (client_uuid ~* '^foo bar so what'::text)
I find myself wishing I could just put a SQL HINT on the query to force the
index to be used but I understand that HINTs are considered harmful and are
therefore not provided for PostgreSQL, so what is the recommended way to
solve this?
thank you very much
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT ignoring index even though ORDER BY and LIMIT present
@ 2010-06-02 22:25 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Jori Jovanovich <jori@dimensiology.com>
3 siblings, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2010-06-02 22:25 UTC (permalink / raw)
To: Jori Jovanovich <jori@dimensiology.com>; pgsql-performance
Jori Jovanovich <jori@dimensiology.com> wrote:
> what is the recommended way to solve this?
The recommended way is to adjust your costing configuration to
better reflect your environment. What version of PostgreSQL is
this? What do you have set in your postgresql.conf file? What does
the hardware look like? How big is the active (frequently
referenced) portion of your database?
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT ignoring index even though ORDER BY and LIMIT present
@ 2010-06-02 22:27 Szymon Guz <mabewlun@gmail.com>
parent: Jori Jovanovich <jori@dimensiology.com>
3 siblings, 0 replies; 7+ messages in thread
From: Szymon Guz @ 2010-06-02 22:27 UTC (permalink / raw)
To: Jori Jovanovich <jori@dimensiology.com>; +Cc: pgsql-performance
2010/6/2 Jori Jovanovich <jori@dimensiology.com>
> hi,
>
> I have a problem space where the main goal is to search backward in time
> for events. Time can go back very far into the past, and so the
> table can get quite large. However, the vast majority of queries are all
> satisfied by relatively recent data. I have an index on the row creation
> date and I would like almost all of my queries to have a query plan looking
> something like:
>
>
>
[CUT]
Do you have autovacuum running? Have you tried updating statistics?
regards
Szymon Guz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT ignoring index even though ORDER BY and LIMIT present
@ 2010-06-02 22:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2010-06-02 22:41 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Jori Jovanovich <jori@dimensiology.com>; pgsql-performance
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Jori Jovanovich <jori@dimensiology.com> wrote:
>> what is the recommended way to solve this?
> The recommended way is to adjust your costing configuration to
> better reflect your environment.
Actually, it's probably not the costs so much as the row estimates.
For instance, that first query was estimated to select 20 out of a
possible 24 rows. If 24 is indeed the right number of matches, then
the planner is right and the OP is wrong: the indexscan is going to
have to traverse almost all of the table and therefore it will be a
lot slower than seqscan + sort. Now, if the real number of matches
is a lot more than that, then the indexscan would make sense because it
could be expected to get stopped by the LIMIT before it has to traverse
too much of the table. So the true problem is to get the rowcount
estimate to line up with reality.
Unfortunately the estimates for ~* are typically not very good.
If you could convert that to plain ~ (case sensitive) it'd probably
work better. Also, if this isn't a particularly modern version of
Postgres, a newer version might do a bit better with the estimate.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT ignoring index even though ORDER BY and LIMIT present
@ 2010-06-03 01:49 Bob Lunney <bob_lunney@yahoo.com>
parent: Jori Jovanovich <jori@dimensiology.com>
3 siblings, 1 reply; 7+ messages in thread
From: Bob Lunney @ 2010-06-03 01:49 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, Jori Jovanovich <jori@dimensiology.com>
Jori,
What is the PostgreSQL version/shared_buffers/work_mem/effective_cache_size/default_statistics_target? Are the statistics for the table up to date? (Run analyze verbose <tablename> to update them.) Table and index structure would be nice to know, too.
If all else fails you can set enable_seqscan = off for the session, but that is a Big Hammer for what is probably a smaller problem.
Bob Lunney
--- On Wed, 6/2/10, Jori Jovanovich <jori@dimensiology.com> wrote:
From: Jori Jovanovich <jori@dimensiology.com>
Subject: [PERFORM] SELECT ignoring index even though ORDER BY and LIMIT present
To: pgsql-performance@postgresql.org
Date: Wednesday, June 2, 2010, 4:28 PM
hi,
I have a problem space where the main goal is to search backward in time for events. Time can go back very far into the past, and so the
table can get quite large. However, the vast majority of queries are all satisfied by relatively recent data. I have an index on the row creation date and I would like almost all of my queries to have a query plan looking something like:
Limit ... -> Index Scan Backward using server_timestamp_idx on events (cost=0.00..623055.91 rows=8695 width=177)
...
However, PostgreSQL frequently tries to do a full table scan. Often what controls whether a scan is performed or not is dependent on the size of the LIMIT and how detailed the WHERE clause is. In practice, the scan is always the wrong answer for my use cases (where "always" is defined to be >99.9%).
Some examples:
(1) A sample query that devolves to a full table scan
EXPLAIN
SELECT events.id, events.client_duration, events.message, events.created_by, events.source, events.type, events.event, events.environment,
events.server_timestamp, events.session_id, events.reference, events.client_uuid FROM events
WHERE client_uuid ~* E'^foo bar so what' ORDER BY server_timestamp DESC
LIMIT 20; QUERY PLAN (BAD!)
-------------------------------------------------------------------------- Limit (cost=363278.56..363278.61 rows=20 width=177)
-> Sort (cost=363278.56..363278.62 rows=24 width=177) Sort Key: server_timestamp
-> Seq Scan on events (cost=0.00..363278.01 rows=24 width=177)
Filter: (client_uuid ~* '^foo bar so what'::text)
(2) Making the query faster by making the string match LESS specific (odd, seems like it should be MORE)
EXPLAIN
SELECT events.id, events.client_duration, events.message, events.created_by, events.source, events.type, events.event, events.environment,
events.server_timestamp, events.session_id, events.reference, events.client_uuid FROM events
WHERE client_uuid ~* E'^foo' ORDER BY server_timestamp DESC
LIMIT 20; QUERY PLAN (GOOD!)
------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..1433.14 rows=20 width=177) -> Index Scan Backward using server_timestamp_idx on events (cost=0.00..623055.91 rows=8695 width=177)
Filter: (client_uuid ~* '^foo'::text)
(3) Alternatively making the query faster by using a smaller limit
EXPLAIN
SELECT events.id, events.client_duration, events.message, events.created_by, events.source, events.type, events.event, events.environment,
events.server_timestamp, events.session_id, events.reference, events.client_uuid FROM events
WHERE client_uuid ~* E'^foo bar so what' ORDER BY server_timestamp DESC
LIMIT 10;
QUERY PLAN (GOOD!) ----------------------------------------------------------------------------------------------------------
Limit (cost=0.00..259606.63 rows=10 width=177) -> Index Scan Backward using server_timestamp_idx on events (cost=0.00..623055.91 rows=24 width=177)
Filter: (client_uuid ~* '^foo bar so what'::text)
I find myself wishing I could just put a SQL HINT on the query to force the index to be used but I understand that HINTs are considered harmful and are therefore not provided for PostgreSQL, so what is the recommended way to solve this?
thank you very much
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT ignoring index even though ORDER BY and LIMIT present
@ 2010-06-03 10:15 Matthew Wakeling <matthew@flymine.org>
parent: Jori Jovanovich <jori@dimensiology.com>
3 siblings, 0 replies; 7+ messages in thread
From: Matthew Wakeling @ 2010-06-03 10:15 UTC (permalink / raw)
To: Jori Jovanovich <jori@dimensiology.com>; +Cc: pgsql-performance
On Wed, 2 Jun 2010, Jori Jovanovich wrote:
> (2) Making the query faster by making the string match LESS specific (odd,
> seems like it should be MORE)
No, that's the way round it should be. The LIMIT changes it all. Consider
if you have a huge table, and half of the entries match your WHERE clause.
To fetch the ORDER BY ... LIMIT 20 using an index scan would involve
accessing only on average 40 entries from the table referenced by the
index. Therefore, the index is quick. However, consider a huge table that
only has twenty matching entries. The index scan would need to touch every
single row in the table to return the matching rows, so a sequential scan,
filter, and sort would be much faster. Of course, if you had an index
capable of answering the WHERE clause, that would be even better for that
case.
Matthew
--
Don't criticise a man until you have walked a mile in his shoes; and if
you do at least he will be a mile behind you and bare footed.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT ignoring index even though ORDER BY and LIMIT present
@ 2010-06-03 15:32 Jori Jovanovich <jori@dimensiology.com>
parent: Bob Lunney <bob_lunney@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jori Jovanovich @ 2010-06-03 15:32 UTC (permalink / raw)
To: pgsql-performance
hi,
I'm sorry for not posting this first.
The server is the following and is being used exclusively for this
PostgreSQL instance:
PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.2.real (GCC)
4.2.4 (Ubuntu 4.2.4-1ubuntu4), 64-bit
Amazon EC2 Large Instance, 7.5GB memory, 64-bit
This is what is set in my postgresql.conf file:
max_connections = 100
ssl = true
shared_buffers = 24MB
ANALYZE VERBOSE EVENTS;
INFO: analyzing "public.events"
INFO: "events": scanned 30000 of 211312 pages, containing 1725088 live rows
and 0 dead rows; 30000 rows in sample, 12151060 estimated total rows
Updating statistics did not effect the results -- it's still doing full
table scans (I had run statistics as well before posting here as well so
this was expected).
thank you
On Wed, Jun 2, 2010 at 8:49 PM, Bob Lunney <bob_lunney@yahoo.com> wrote:
> Jori,
>
> What is the PostgreSQL
> version/shared_buffers/work_mem/effective_cache_size/default_statistics_target?
> Are the statistics for the table up to date? (Run analyze verbose
> <tablename> to update them.) Table and index structure would be nice to
> know, too.
>
> If all else fails you can set enable_seqscan = off for the session, but
> that is a Big Hammer for what is probably a smaller problem.
>
> Bob Lunney
>
> --- On *Wed, 6/2/10, Jori Jovanovich <jori@dimensiology.com>* wrote:
>
>
> From: Jori Jovanovich <jori@dimensiology.com>
> Subject: [PERFORM] SELECT ignoring index even though ORDER BY and LIMIT
> present
> To: pgsql-performance@postgresql.org
> Date: Wednesday, June 2, 2010, 4:28 PM
>
>
> hi,
>
> I have a problem space where the main goal is to search backward in time
> for events. Time can go back very far into the past, and so the
> table can get quite large. However, the vast majority of queries are all
> satisfied by relatively recent data. I have an index on the row creation
> date and I would like almost all of my queries to have a query plan looking
> something like:
>
> Limit ...
> -> Index Scan Backward using server_timestamp_idx on events
> (cost=0.00..623055.91 rows=8695 width=177)
> ...
>
> However, PostgreSQL frequently tries to do a full table scan. Often what
> controls whether a scan is performed or not is dependent on the size of the
> LIMIT and how detailed the WHERE clause is. In practice, the scan is always
> the wrong answer for my use cases (where "always" is defined to be >99.9%).
>
> Some examples:
>
> (1) A sample query that devolves to a full table scan
>
> EXPLAIN
> SELECT events.id, events.client_duration, events.message,
> events.created_by, events.source, events.type, events.event,
> events.environment,
> events.server_timestamp, events.session_id, events.reference,
> events.client_uuid
> FROM events
> WHERE client_uuid ~* E'^foo bar so what'
> ORDER BY server_timestamp DESC
> LIMIT 20;
> QUERY PLAN (BAD!)
> --------------------------------------------------------------------------
> Limit (cost=363278.56..363278.61 rows=20 width=177)
> -> Sort (cost=363278.56..363278.62 rows=24 width=177)
> Sort Key: server_timestamp
> -> Seq Scan on events (cost=0.00..363278.01 rows=24 width=177)
> Filter: (client_uuid ~* '^foo bar so what'::text)
>
>
> (2) Making the query faster by making the string match LESS specific (odd,
> seems like it should be MORE)
>
> EXPLAIN
> SELECT events.id, events.client_duration, events.message,
> events.created_by, events.source, events.type, events.event,
> events.environment,
> events.server_timestamp, events.session_id, events.reference,
> events.client_uuid
> FROM events
> WHERE client_uuid ~* E'^foo'
> ORDER BY server_timestamp DESC
> LIMIT 20;
> QUERY PLAN (GOOD!)
>
>
> ------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..1433.14 rows=20 width=177)
> -> Index Scan Backward using server_timestamp_idx on events
> (cost=0.00..623055.91 rows=8695 width=177)
> Filter: (client_uuid ~* '^foo'::text)
>
>
> (3) Alternatively making the query faster by using a smaller limit
>
> EXPLAIN
> SELECT events.id, events.client_duration, events.message,
> events.created_by, events.source, events.type, events.event,
> events.environment,
> events.server_timestamp, events.session_id, events.reference,
> events.client_uuid
> FROM events
> WHERE client_uuid ~* E'^foo bar so what'
> ORDER BY server_timestamp DESC
> LIMIT 10;
> QUERY PLAN (GOOD!)
>
>
> ----------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..259606.63 rows=10 width=177)
> -> Index Scan Backward using server_timestamp_idx on events
> (cost=0.00..623055.91 rows=24 width=177)
> Filter: (client_uuid ~* '^foo bar so what'::text)
>
>
> I find myself wishing I could just put a SQL HINT on the query to force the
> index to be used but I understand that HINTs are considered harmful and are
> therefore not provided for PostgreSQL, so what is the recommended way to
> solve this?
>
> thank you very much
>
>
On Thu, Jun 3, 2010 at 5:15 AM, Matthew Wakeling <matthew@flymine.org>
wrote:
> On Wed, 2 Jun 2010, Jori Jovanovich wrote:
>
>> (2) Making the query faster by making the string match LESS specific (odd,
>> seems like it should be MORE)
>>
>
> No, that's the way round it should be. The LIMIT changes it all. Consider
> if you have a huge table, and half of the entries match your WHERE clause.
> To fetch the ORDER BY ... LIMIT 20 using an index scan would involve
> accessing only on average 40 entries from the table referenced by the index.
> Therefore, the index is quick. However, consider a huge table that only has
> twenty matching entries. The index scan would need to touch every single row
> in the table to return the matching rows, so a sequential scan, filter, and
> sort would be much faster. Of course, if you had an index capable of
> answering the WHERE clause, that would be even better for that case.
>
Okay, this makes sense, thank you -- I was thinking about it backwards.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-06-03 15:32 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-02 20:28 SELECT ignoring index even though ORDER BY and LIMIT present Jori Jovanovich <jori@dimensiology.com>
2010-06-02 22:25 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-02 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-02 22:27 ` Szymon Guz <mabewlun@gmail.com>
2010-06-03 01:49 ` Bob Lunney <bob_lunney@yahoo.com>
2010-06-03 15:32 ` Jori Jovanovich <jori@dimensiology.com>
2010-06-03 10:15 ` Matthew Wakeling <matthew@flymine.org>
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