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

Re: Query Plan - Index Scan & Seq Scan

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: dbadmin(at)nqadmin(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Query Plan - Index Scan & Seq Scan
Date: 2005-05-12 15:23:36
Message-ID: (view raw or whole thread)
Lists: pgsql-admin
On Thu, 2005-05-12 at 10:05, Prasanth wrote:
> When joining two tables the query plan is doing a seq scan rather than index
> scan. I do have indexes on the columns used for joining the tables.
> Example:
>                                        QUERY PLAN
> -----------------------------------------------------------------------------------------
>  Hash Join  (cost=13865.30..326413.23 rows=6451 width=18)
>    Hash Cond: ("outer".id = "inner".id)
>    ->  Seq Scan on a  (cost=0.00..125076.37 rows=6450937 width=18)
>    ->  Hash  (cost=10168.64..10168.64 rows=500664 width=4)
>          ->  Seq Scan on b (cost=0.00..10168.64 rows=500664 width=4)
> (5 rows)
> The planner used to perform a index scan. I have added a lot of data in those
> two tables. Right now both tables have millions of records. After adding the new
> records the planner is going for a seq scan while doing the join.
> Is there any tunning I can do so that the query planner would do a index scan?
> I did a vacuum analyze but no change.

try this:

explain analyze SELECT FROM a, b WHERE =;
set enable_seqscan=off;
explain analyze SELECT FROM a, b WHERE =;

and see which is faster.

It's quite likely that using an index here makes no sense, since there's
no selectivity happening, and you need all the data anyway.

In response to


pgsql-admin by date

Next:From: Gourish SingbalDate: 2005-05-12 15:25:35
Subject: Help with connections
Previous:From: Scott MarloweDate: 2005-05-12 15:20:53
Subject: Re: memory allocation ; postgresql-8.0

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