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

Re: Postgres 8.3 only uses seq scan

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Clemens Schwaighofer" <clemens(dot)schwaighofer(at)tequila(dot)jp>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Postgres 8.3 only uses seq scan
Date: 2008-11-26 05:04:13
Message-ID: dcc563d10811252104m6731cf78x32f15a8a435a33cb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer
<clemens(dot)schwaighofer(at)tequila(dot)jp> wrote:
> but on the 8.3 version i get this back
>
> # explain select * from foo f, bar b where f.foo_id = b.foo_id;
>                            QUERY PLAN
> ------------------------------------------------------------------
>  Hash Join  (cost=1.07..2.14 rows=3 width=24)
>   Hash Cond: (b.foo_id = f.foo_id)
>   ->  Seq Scan on bar b  (cost=0.00..1.03 rows=3 width=14)
>   ->  Hash  (cost=1.03..1.03 rows=3 width=10)
>         ->  Seq Scan on foo f  (cost=0.00..1.03 rows=3 width=10)

Of course it uses a seq scan.  All the data fits handily into a single
page I assume.

> once I insert a million rows he does use the index:
>
> # explain select * from foo f, bar b where f.foo_id = b.foo_id;
>                                    QUERY PLAN
> -----------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..26.39 rows=9 width=35)
>   ->  Seq Scan on foo f  (cost=0.00..1.03 rows=3 width=21)
>   ->  Index Scan using bar_foo_id_idx on bar b  (cost=0.00..8.42 rows=3
> width=14)
>         Index Cond: (b.foo_id = f.foo_id)

I don't see a million rows here, only three.  Have you run analyze
after loading all that data?  Or is it retrieving 3 rows out of a
million?  If so then an index scan does make sense.

In response to

Responses

pgsql-general by date

Next:From: Clemens SchwaighoferDate: 2008-11-26 05:07:21
Subject: Re: Postgres 8.3 only uses seq scan
Previous:From: Andrew SullivanDate: 2008-11-26 04:47:56
Subject: Re: slow, long-running 'commit prepared'

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