We have problems with performance of a simple SQL statement.
If we add a LIMIT 50, the query is about 6 times slower than without a limit (query returns 2 rows).
I have read this discussion: http://archives.postgresql.org/pgsql-performance/2008-09/msg00005.php but there seems to be no solution in it.
I tried this things: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server but changing settings doesn't have significant effect.
The DDL statements (create tables, indices) are attached.
The events_events table contains 375K rows, the events_event_types contains 71 rows.
The query:
select events_events.id FROM events_events
left join events_event_types on events_events.eventType_id=events_event_types.id
where events_event_types.severity=70
and events_events.cleared='f'
order by events_events.dateTime DESC
It takes 155ms to run this query (returning 2 rows)
After adding LIMIT 10, it takes 950 ms to run.
Query plan: without limit:
"Sort (cost=20169.62..20409.50 rows=95952 width=16)"
" Sort Key: events_events.datetime"
" -> Hash Join (cost=2.09..12229.58 rows=95952 width=16)"
" Hash Cond: (events_events.eventtype_id = events_event_types.id)"
" -> Seq Scan on events_events (cost=0.00..9918.65 rows=359820 width=24)"
" Filter: (NOT cleared)"
" -> Hash (cost=1.89..1.89 rows=16 width=8)"
" -> Seq Scan on events_event_types (cost=0.00..1.89 rows=16 width=8)"
" Filter: (severity = 70)"
Query plan: with limit:
"Limit (cost=0.00..12.50 rows=10 width=16)"
" -> Nested Loop (cost=0.00..119932.21 rows=95952 width=16)"
" -> Index Scan Backward using events_events_datetime_ind on events_events (cost=0.00..18242.28 rows=359820 width=24)"
" Filter: (NOT cleared)"
" -> Index Scan using events_event_types_pkey on events_event_types (cost=0.00..0.27 rows=1 width=8)"
" Index Cond: (events_event_types.id = events_events.eventtype_id)"
" Filter: (events_event_types.severity = 70)"
So postgres seems to handle a query with limit different internally. Tried to set default_statistics_target to 10, 100, 200, but no significant differences.
This problem appears on both Postgres 8.3 and 8.4.
Any suggestions?
Thanks in advance!
Best regards,
Kees van Dieren
--
Squins | IT, Honestly
Oranjestraat 23
2983 HL Ridderkerk
The Netherlands
Phone: +31 (0)180 414520
Mobile: +31 (0)6 30413841
www.squins.com
Chamber of commerce Rotterdam: 22048547
Attachment:
events_schema.sql
Description: Binary data
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
