Hi Folks, Thanks for your response.
I have added the following index (suggested by other post): CREATE INDEX events_events_cleared_eventtype ON events_events USING btree (eventtype_id, cleared) WHERE cleared = false; Also with columns in reversed order. No changes in response time noticed. Index on cleared column already is there (indices are in sql file attached to initial post.). eventtype_id has a foreign key constraint, which adds an index automatically I believe? The explain analyze results for both queries: explain analyze 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 not events_events.cleared order by events_events.dateTime DESC LIMIT 100 >>> "Limit (cost=0.00..125.03 rows=100 width=16) (actual time=0.046..3897.094 rows=77 loops=1)" " -> Nested Loop (cost=0.00..120361.40 rows=96269 width=16) (actual time=0.042..3896.881 rows=77 loops=1)" " -> Index Scan Backward using events_events_datetime_ind on events_events (cost=0.00..18335.76 rows=361008 width=24) (actual time=0.025..720.345 rows=360637 loops=1)" " Filter: (NOT cleared)" " -> Index Scan using events_event_types_pkey on events_event_types (cost=0.00..0.27 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=360637)" " Index Cond: (events_event_types.id = events_events.eventtype_id)" " Filter: (events_event_types.severity = 70)" "Total runtime: 3897.268 ms" explain analyze 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 not events_events.cleared order by events_events.dateTime DESC >>> "Sort (cost=20255.18..20495.85 rows=96269 width=16) (actual time=1084.842..1084.951 rows=77 loops=1)" " Sort Key: events_events.datetime" " Sort Method: quicksort Memory: 20kB" " -> Hash Join (cost=2.09..12286.62 rows=96269 width=16) (actual time=1080.789..1084.696 rows=77 loops=1)" " Hash Cond: (events_events.eventtype_id = events_event_types.id)" " -> Seq Scan on events_events (cost=0.00..9968.06 rows=361008 width=24) (actual time=0.010..542.946 rows=360637 loops=1)" " Filter: (NOT cleared)" " -> Hash (cost=1.89..1.89 rows=16 width=8) (actual time=0.077..0.077 rows=16 loops=1)" " -> Seq Scan on events_event_types (cost=0.00..1.89 rows=16 width=8) (actual time=0.010..0.046 rows=16 loops=1)" " Filter: (severity = 70)" "Total runtime: 1085.145 ms" Any suggestions? Thanks in advance! Best regards, Kees van Dieren pgsql-performance@postgresql.org 2009/7/31 Greg Stark <gsst...@mit.edu> > On Fri, Jul 31, 2009 at 1:11 PM, Kees van Dieren<keesvandie...@gmail.com> > wrote: > > It takes 155ms to run this query (returning 2 rows) > > > > Query plan: without limit: > > "Sort (cost=20169.62..20409.50 rows=95952 width=16)" > > Could you send the results of EXPLAIN ANALYZE for both queries? > Evidently the planner is expecting a lot more rows than the 2 rows > you're expecting but it's not clear where it's gone wrong. > > > -- > greg > http://mit.edu/~gsstark/resume.pdf <http://mit.edu/%7Egsstark/resume.pdf> > -- 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
