> -----Urspr√ľngliche Nachricht-----
> [mailto:[EMAIL PROTECTED] 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 
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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not

Reply via email to