Hi folks, 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
events_schema.sql
Description: Binary data
-- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
