[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: query optimization





On 04/26/2012 04:08 PM, Tom Lane wrote:
Thomas Kellerer<spam_eater@xxxxxxx>  writes:
Tom Lane wrote on 26.04.2012 21:17:
Um ... did you analyze all the tables, or just some of them?  I get
sub-millisecond runtimes if all four tables have been analyzed, but it
does seem to pick lousy plans if, say, only a and b have been analyzed.
Here it's similar to Richard's experience:
Before analyzing the four tables, the first statement yields this plan:
[ merge joins ]
This continues to stay the plan for about 10-15 repetitions, then it turns to this plan
[ hash joins ]
Hmm.  I see it liking the merge-join plan (with minor variations) with
or without analyze data, but if just some of the tables have been
analyzed, it goes for the hash plan which is a good deal slower.  The
cost estimates aren't that far apart though.  In any case, the only
reason the merge join is so fast is that the data is perfectly ordered
in each table; on a less contrived example, it could well be a lot
slower.


It's not so terribly contrived, is it? It's common enough to have tables which are append-only and to join them by something that corresponds to the append order (serial field, timestamp etc.)

cheers

andrew

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Home]     [Yosemite]

Powered by Linux