Re: [PERFORM] Limit + group + join

2005-08-29 Thread Merlin Moncure
Tobias wrote: Splendid :-) Unfortunately we will not be upgrading for some monthes still, but anyway I'm happy. This provides yet another good argument for upgrading sooner. I'm also happy to see such a perfect match: - A problem that can be reduced from beeing complex and

Re: [PERFORM] Limit + group + join

2005-08-27 Thread Tom Lane
Mark Kirkwood [EMAIL PROTECTED] writes: joinlimit=# EXPLAIN SELECT c.id FROM c JOIN b ON c_id=c.id GROUP BY c.id ORDER BY c.id DESC LIMIT 5; [ fails to pick an available index-scan-backward plan ] I looked into this and found that indeed the desirable join plan was getting generated, but it

Re: [PERFORM] Limit + group + join

2005-08-27 Thread Mark Kirkwood
Tom Lane wrote: I looked into this and found that indeed the desirable join plan was getting generated, but it wasn't picked because query_planner didn't have an accurate idea of how much of the join needed to be scanned to satisfy the GROUP BY step. I've committed some changes that hopefully

Re: [PERFORM] Limit + group + join

2005-08-27 Thread Tobias Brox
[Tom Lane] I looked into this and (...) I've committed some changes that hopefully will let 8.1 be smarter about GROUP BY ... LIMIT queries. [Mark Kirkwood] Very nice :-) (...) This is 8.1devel from today. Splendid :-) Unfortunately we will not be upgrading for some monthes still, but anyway

Re: [PERFORM] Limit + group + join

2005-08-26 Thread Tom Lane
Mark Kirkwood [EMAIL PROTECTED] writes: What is interesting is why this plan is being rejected... Which PG version are you using exactly? That mistake looks like an artifact of the 8.0 fuzzy plan cost patch, which we fixed recently:

Re: [PERFORM] Limit + group + join

2005-08-26 Thread Mark Kirkwood
Tom Lane wrote: Mark Kirkwood [EMAIL PROTECTED] writes: What is interesting is why this plan is being rejected... Which PG version are you using exactly? That mistake looks like an artifact of the 8.0 fuzzy plan cost patch, which we fixed recently:

Re: [PERFORM] Limit + group + join

2005-08-26 Thread Tom Lane
Mark Kirkwood [EMAIL PROTECTED] writes: That doesn't explain why the nested loop is being kicked tho', No, but I think the fuzzy-cost bug does. There are two different issues here. regards, tom lane ---(end of

Re: [PERFORM] Limit + group + join

2005-08-26 Thread Mark Kirkwood
Interestingly enough, 7.4.8 and 8.1devel-2005-08-23 all behave the same as 8.0.3 for me (tables freshly ANALYZEd): joinlimit=# SELECT version(); version

Re: [PERFORM] Limit + group + join

2005-08-26 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: As far as the desc point goes, the problem is that mergejoins aren't capable of dealing with backward sort order, so a merge plan isn't considered for that case (or at least, it would have to have a sort after it, which pretty much defeats the point for a

Re: [PERFORM] Limit + group + join

2005-08-25 Thread Tobias Brox
[Jeffrey W. Baker - Thu at 06:56:59PM -0700] explain select c.id from c join b on c_id=c.id group by c.id order by c.id desc limit 5; Where's b in this join clause? join b on c_id=c.id It just a funny way of writing: select c.id from c,b where c_id=c.id group by c.id order by c.id desc

Re: [PERFORM] Limit + group + join

2005-08-25 Thread Jeffrey W. Baker
On Thu, 2005-08-25 at 18:56 -0700, Jeffrey W. Baker wrote: On Fri, 2005-08-26 at 02:27 +0200, Tobias Brox wrote: Consider this setup - which is a gross simplification of parts of our production system ;-) create table c (id integer primary key); create table b (id integer primary