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

On 17 Aug 2006 at 14:33, Tom Lane wrote:

> I wrote:
> > Anywy, your point about the sort being redundant is a good one, and
> > offhand I'd have expected PG to catch that; I'll have to look into
> > why it didn't. But that's not going to explain a 10x speed
> > difference, because the sort isn't 90% of the runtime.
>
> I dug into this using some made-up test data, and was able to reproduce
> the plan you got after changing the order of the pkey index columns
> to (regn_no, transfer_date, flock_no) ... are you sure you quoted that
> accurately before?

Yes. Maybe the data I've uploaded to www.shetland-
sheep.org.uk/pgdata/sheep_flock.zip will help reproduce the plan.

<snip>
> I found a couple of minor planner problems, which I've repaired in CVS
> HEAD. You might consider using TEXT columns instead of VARCHAR(n),
> because the only bug that actually seemed to change the chosen plan
> involved the planner getting confused by the difference between
> varchar_var and varchar_var::text (which is what gets generated for
> sorting purposes because varchar doesn't have a separate sort operator).

As someone else suggested, these fields ought really to be CHAR no VARCHAR.
I chose VARCHAR because the data mostly is shorter than the maximum lengths
(although probably not enough to matter). I'd not really got into the subtleties of
different behaviour of CHAR and VARCHAR.
>
<snip>

Regards,--
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==