SQL select query becomes slow when using limit (with no offset)

[Kees van Dieren <keesvandieren(at)gmail(dot)com>]

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

Attachment: events_schema.sql
Description: Binary data