Skip site navigation (1) Skip section navigation (2)

Re: Explain explained

From: "Markus Stocker" <markus(at)wilabs(dot)ch>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Explain explained
Date: 2008-03-04 22:59:45
Message-ID: a9dfaf710803041459n78c99899s805195028e3db45a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: johnfDate: 2008-03-06 05:10:51
Subject: use SSL TO AUTH
Previous:From: Tom LaneDate: 2008-03-04 22:30:31
Subject: Re: Explain explained

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group