Re: PostgreSQL runs a query much slower than BDE and MySQL - Mailing list pgsql-performance

On 16 Aug 2006 at 20:02, Arjen van der Meijden wrote:

> On 16-8-2006 18:48, Peter Hardman wrote:
> > Using identically structured tables and the same primary key, if I run this on
> > Paradox/BDE it takes about 120ms, on MySQL (5.0.24, local server) about 3ms,
> > and on PostgresSQL (8.1.3, local server) about 1290ms). All on the same
> > Windows XP Pro machine with 512MB ram of which nearly half is free.
>
> Is that with or without query caching? I.e. can you test it with SELECT
> SQL_NO_CACHE ... ?
> In a read-only environment it will still beat PostgreSQL, but as soon as
> you'd get a read-write environment, MySQL's query cache is of less use.
> So you should compare both the cached and non-cached version, if applicable.
It seems to make no difference - not surprising really as I'm just running the query
from the command line interface.
>
> Besides that, most advices on this list are impossible without the
> result of 'explain analyze', so you should probably get that as well.
Here is the output of EXPLAIN ANALYZE for the slow query:

Unique  (cost=7201.65..8487.81 rows=1 width=13) (actual
time=1649.733..1811.684 rows=32 loops=1)
  ->  Merge Join  (cost=7201.65..8487.80 rows=1 width=13) (actual
time=1649.726..1811.528 rows=32 loops=1)
        Merge Cond: ((("outer".regn_no)::text = "inner"."?column3?") AND
("outer".transfer_date = "inner".last_xfer_date))
        ->  Index Scan using sheep_flock_pkey on sheep_flock f1
(cost=0.00..1033.19 rows=77 width=13) (actual time=15.357..64.237 rows=127
loops=1)
              Index Cond: ((flock_no)::text = '1359'::text)
        ->  Sort  (cost=7201.65..7285.84 rows=33676 width=15) (actual
time=1580.198..1653.502 rows=38277 loops=1)
              Sort Key: (f2.regn_no)::text, f2.last_xfer_date
              ->  Subquery Scan f2  (cost=0.00..4261.67 rows=33676 width=15) (actual
time=0.331..598.246 rows=38815 loops=1)
                    ->  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
rows=81802 loops=1)
Total runtime: 1812.737 ms


>
> I'm not sure whether this is the same query, but you might want to try:
> SELECT DISTINCT f1.regn_no, f1.transfer_date as date_in
> FROM SHEEP_FLOCK f1
> WHERE
> f1.flock_no = '1359'
> AND f1.transfer_date = (SELECT MAX(f.transfer_date) FROM SHEEP_FLOCK f
> WHERE regn_no = f1.regn_no)
>
That's neat - I didn't know you could make a reference from a subselect to the
outer select. Your query has the same performance as my very complex one on
both MySQL and PostgreSQL. However I'm not entirely sure about the times for
MySQL - every interface gives a different answer so I'll have to try them from a
script so I know whats going on.
Interestingly BDE takes 7 seconds to run your query. Just as well I didn't start
from there...
> And you might need an index on (regn_no, transfer_date) and/or one
> combined with that flock_no.
Explain says it only uses the primary key, so it seems there' no need for a
separate index

Thanks for the help
--
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==