From: Sam Mason <sam@samason.me.uk>
To: pgsql-performance@postgresql.org
Subject: Re: planner picking more expensive plan
Date: Fri, 1 Jul 2005 16:58:30 +0100
Message-ID: <20050701155829.GX62747@colo.samason.me.uk> (raw)
In-Reply-To: <42C55EA0.1000404@arbash-meinel.com>
References: <20050701133305.GB2623@sam.lan.samason.me.uk>
<42C55EA0.1000404@arbash-meinel.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sam@samason.me.uk
Subject: Re: planner picking more expensive plan
In-Reply-To: <20050701155829.GX62747@colo.samason.me.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox