On 16 Aug 2006 at 18:51, Tom Lane wrote: > "Peter Hardman" <[EMAIL PROTECTED]> writes: > > I'm in the process of migrating a Paradox 7/BDE 5.01 database from > > single-user <snip>
Arjen van der Meijden has proposed a very elegant query in another post. > What I find interesting though is that it sounds like both MSSQL and > Paradox know something we don't about how to optimize it. PG doesn't > have any idea how to do the above query without forming the full output > of the sub-select, but I suspect that the commercial DBs know a > shortcut; perhaps they are able to automatically derive a restriction > in the subquery similar to what you did by hand. Does Paradox have > anything comparable to EXPLAIN that would give a hint about the query > plan they are using? Sadly, no. In fact the ability to use SQL from Paradox at all is not well known and not very visible in the the documentation. I wonder whether Paradox and MySQL are just not doing the sort (this seems to be what eats up the time), since the output of the subquery is in fact already in the proper order. > > Also, just as in the other thread, I'm thinking that a seqscan+hash > aggregate would be a better idea than this bit: > > > -> GroupAggregate (cost=0.00..3924.91 rows=33676 > > width=13) (actual time=0.324..473.131 rows=38815 loops=1) > > -> Index Scan using sheep_flock_pkey on > > sheep_flock f (cost=0.00..3094.95 rows=81802 width=13) (actual > > time=0.295..232.156) > > Possibly you need to raise work_mem to get it to consider the hash > aggregation method. > > BTW, are you *sure* you are testing PG 8.1? The "Subquery Scan f2" plan > node looks unnecessary to me, and I'd have expected 8.1 to drop it out. > 8.0 and before would have left it in the plan though. This doesn't make > all that much difference performance-wise in itself, but it does make me > wonder what you are testing. Yes, the executables all say version 8.1.3.6044 > Regards,-- Peter Hardman Acre Cottage, Horsebridge King's Somborne Stockbridge SO20 6PT == Breeder of Shetland Cattle and Shetland Sheep == ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
