Re: Explain explained

On Tue, Mar 4, 2008 at 5:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Markus Stocker" <markus(at)wilabs(dot)ch> writes:
> > On Tue, Mar 4, 2008 at 1:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> >>> 2/ Sequential scans seem to me more expensive compared to index scans.
> >>> I'm wondering why the sequential scan on individual_name is the first
> >>> executed in the plan.
> >>
> >> I was wondering that too; it looks like it should be a candidate for an
> >> index search. Datatype problem maybe? Again, you've not shown us the
> >> table definitions...
>
> > I guess this is explained too now, at least partially.
>
> No, I meant it seemed like that should have been an indexscan; fetching
> one row via an index should have an estimated cost much less than 400.
>
> What do you get if you just do
> explain select * from individual_name where name = 'http://www.University0.edu'
> If it still says seqscan, what if you force it with
> set enable_seqscan = off?

Sorry, I haven't been accurate in my explanations.

If I have an index on individual_name.name then postgresql always
executes an index scan. If I drop it, then it uses a sequential scan
and if I then force it with enable_seqscan = off I get this

QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on individual_name (cost=100000000.00..100000430.67 rows=1 width=68)
Filter: (name = 'http://www.University0.edu'::text)

So, the query plan in my first email with the sequential scan was
because the index on individual_name.name was dropped. Sorry about
that.

markus