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

Peter, I compared these using the data you supplied on my PostgreSQL
8.1.4 system:

On Aug 17, 2006, at 12:09 PM, Scott Lamb wrote:

> On Aug 16, 2006, at 3:51 PM, Tom Lane wrote:
>>> /* Select all sheep who's most recent transfer was into the
>>> subject flock */
>>> SELECT DISTINCT f1.regn_no, f1.transfer_date as date_in
>>> FROM SHEEP_FLOCK f1 JOIN
>>> /* The last transfer date for each sheep */
>>> (SELECT f.regn_no, MAX(f.transfer_date) as last_xfer_date
>>> FROM SHEEP_FLOCK f
>>> GROUP BY f.regn_no) f2
>>> ON f1.regn_no = f2.regn_no
>>> WHERE f1.flock_no = '1359'
>>> AND f1.transfer_date = f2.last_xfer_date

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------
Unique (cost=2575.07..2575.08 rows=1 width=36) (actual
time=1083.579..1083.696 rows=32 loops=1)
-> Sort (cost=2575.07..2575.07 rows=1 width=36) (actual
time=1083.576..1083.613 rows=32 loops=1)
Sort Key: f1.regn_no, f1.transfer_date
-> Nested Loop (cost=1364.00..2575.06 rows=1 width=36)
(actual time=287.895..1083.297 rows=32 loops=1)
-> HashAggregate (cost=1364.00..1366.50 rows=200
width=36) (actual time=262.345..337.940 rows=38815 loops=1)
-> Seq Scan on sheep_flock f
(cost=0.00..1116.00 rows=49600 width=36) (actual time=0.005..119.282
rows=81802 loops=1)
-> Index Scan using sheep_flock_pkey on sheep_flock
f1 (cost=0.00..6.02 rows=1 width=36) (actual time=0.016..0.016
rows=0 loops=38815)
Index Cond: (((f1.regn_no)::text =
("outer".regn_no)::text) AND ((f1.flock_no)::text = '1359'::text) AND
(f1.transfer_date = "outer"."?column2?"))
Total runtime: 1085.115 ms
(9 rows)

>>
>> This seems pretty closely related to this recent thread:
>> http://archives.postgresql.org/pgsql-performance/2006-08/msg00220.php
>> in which the OP is doing a very similar kind of query in almost
>> exactly
>> the same way.
>>
>> I can't help thinking that there's probably a better way to phrase
>> this
>> type of query in SQL, though it's not jumping out at me what that is.
>
> I don't know about better, but I tend to phrase these in a quite
> different way that's (hopefully) equivalent:
>
> select latest.regn_no,
> latest.transfer_date as date_in
> from sheep_flock latest
> where not exists (
> select 'x'
> from sheep_flock even_later
> where latest.regn_no = even_later.regn_no
> and latest.transfer_date < even_later.transfer_date)
> and latest.flock_no = '1359'
>
> There's no MAX() or DISTINCT here, so maybe this is easier to
> optimize?

Q
UERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
------
Bitmap Heap Scan on sheep_flock latest (cost=764.60..2185.05
rows=124 width=36) (actual time=11.915..13.800 rows=32 loops=1)
Recheck Cond: ((flock_no)::text = '1359'::text)
Filter: (NOT (subplan))
-> Bitmap Index Scan on sheep_flock_pkey (cost=0.00..764.60
rows=248 width=0) (actual time=10.950..10.950 rows=127 loops=1)
Index Cond: ((flock_no)::text = '1359'::text)
SubPlan
-> Index Scan using sheep_flock_pkey on sheep_flock
even_later (cost=0.00..317.49 rows=83 width=0) (actual
time=0.016..0.016 rows=1 loops=127)
Index Cond: ((($0)::text = (regn_no)::text) AND ($1 <
transfer_date))
Total runtime: 13.902 ms
(9 rows)

seems to return the same data in two orders of magnitude less time.

--
Scott Lamb <http://www.slamb.org/>