Skip site navigation (1) Skip section navigation (2)

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

From: Kees van Dieren <keesvandieren(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: SQL select query becomes slow when using limit (with no offset)
Date: 2009-07-31 12:11:14
Message-ID: 13bb64e70907310511p72c716c5q124d1038c63f2734@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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: application/octet-stream (1.8 KB)

Responses

pgsql-performance by date

Next:From: Greg StarkDate: 2009-07-31 12:42:26
Subject: Re: SQL select query becomes slow when using limit (with no offset)
Previous:From: Kees van DierenDate: 2009-07-31 11:06:39
Subject: SQL select query becomes slow when using limit (with no offset)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group