Hi! > -----Ursprüngliche Nachricht----- > Von: [EMAIL PROTECTED] > [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 Markus ---------------------------(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 match