Re: PostgreSQL runs a query much slower than BDE and MySQL

On 16 Aug 2006 at 18:51, Tom Lane wrote:

> "Peter Hardman" <peter(at)ssbg(dot)zetnet(dot)co(dot)uk> 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 ==