Re: Strange planner decision on quite simple select

From: "Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de>
To: "Richard Huxton" <dev(at)archonet(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Strange planner decision on quite simple select
Date: 2005-10-25 13:39:42
Message-ID: 2266D0630E43BB4290742247C891057508384067@dozer.computec.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


Hi!

> -----Ursprüngliche Nachricht-----
> Von: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] Im Auftrag
> von Richard Huxton
> Gesendet: Dienstag, 25. Oktober 2005 12:07
> An: Markus Wollny
> Cc: pgsql-performance(at)postgresql(dot)org
> Betreff: Re: [PERFORM] Strange planner decision on quite simple select
>
> Hmm - it shouldn't take that long. If I'm reading this right,
> it's expecting to have to fetch 5606 rows to match
> thread_id=3354253 the 20 times you've asked for. Now, what it
> probably doesn't know is that thread_id is correlated with
> message_id quite highly (actually, I don't know that, I'm
> guessing). So - it starts at message_id=1 and works along,
> but I'm figuring that it needs to reach message_id's in the
> 3-4 million range to see any of the required thread.

Reading this I tried with adding a "AND MESSAGE_ID >= THREAD_ID" to the WHERE-clause, as you've guessed quite correctly, both message_id and thread_id are derived from the same sequence and thread_id equals the lowest message_id in a thread. This alone did quite a lot to improve things - I got stable executing times down from an average 12 seconds to a mere 2 seconds - just about the same as with the subselect.

> Suggestions:
> 1. Try "ORDER BY thread_id,message_id" and see if that nudges
> things your way.
> 2. Keep #1 and try replacing the index on (thread_id) with
> (thread_id,message_id)

Did both (though adding such an index during ordinary workload took some time as did the VACUUM ANALYZE afterwards) and that worked like a charm - I've got execution times down to as little as a few milliseconds - wow! Thank you very much for providing such insightful hints!

Kind regards

Markus

Browse pgsql-performance by date

  From Date Subject
Next Message Chris Mair 2005-10-25 13:44:36 insertion of bytea
Previous Message Tom Lane 2005-10-25 13:38:35 Re: Why Index is not working on date columns.