Re: Strange planner decision on quite simple select

Hi!

> -----Ursprüngliche Nachricht-----
> Von: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] Im Auftrag
> von Richard Huxton
> Gesendet: Dienstag, 25. Oktober 2005 12:07
> An: Markus Wollny
> Cc: pgsql-performance@postgresql.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
ina thread. This alone did quite a lot to improve things - I got stable executing times down from an average 12 seconds
toa 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
thatworked like a charm - I've got execution times down to as little as a few milliseconds - wow! Thank you very much
forproviding such insightful hints! 

Kind regards

   Markus