Date: Fri, 1 Jul 2005 16:58:30 +0100
From: Sam Mason <sam@samason.me.uk>
To: pgsql-performance@postgresql.org
Subject: Re: planner picking more expensive plan
Message-ID: <20050701155829.GX62747@colo.samason.me.uk>
Mail-Followup-To: Sam Mason <sam@samason.me.uk>,
	pgsql-performance@postgresql.org
References: <20050701133305.GB2623@sam.lan.samason.me.uk>
	<42C55EA0.1000404@arbash-meinel.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <42C55EA0.1000404@arbash-meinel.com>
User-Agent: Mutt/1.4.2.1i

John A Meinel wrote:
>Why are you using LIMIT without having an ORDER BY?

I'm just exploring the data, trying to figure out what it's like.

>It just seems like this query isn't very useful. As it doesn't restrict
>by animal id, and it just gets 10 randomly selected animals where
>m.mtypeid=0.

Yup, that's the point.  Check to see if the animals were born where
they say they were.  The data's come from an external source and
I'm just trying to figure out how good it is before I do too much
with it

>And why a LEFT JOIN instead of a normal join?

I'm not sure if some animals will have missing data!

>Anyway, the general constraints you are applying seem kind of confusing.

This was a slightly cut down query in an attempt to reduce general
confusion -- I guess I failed.  Sorry!

>I would guess that this would help the planner realize it should try to
>use an index, since it can realize that it wants only a few rows by
>a.animalid in order.

This seems to work the appropiate magic.  It always seems to prefer
index scans now.

The real point of asking this question orignally was to find out
why the planner was choosing a more expensive plan over a cheaper
one.  When I discovered this orignally I was disabling seqscan and
then it picked the correct version.  The actual work_mem didn't
change when I did this, it just picked the correct plan.  I discovered
the work_mem parameter fiddle later.  I think I forgot to mention
that in the original email though!


  Sam