Re: Explain explained

On Tue, Mar 4, 2008 at 5:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Markus Stocker" <markus@wilabs.ch> writes:
>  > On Tue, Mar 4, 2008 at 1:26 PM, Tom Lane <tgl@sss.pgh.pa.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