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: 1115911416.3868.265.camel@state.g2switchworks.com (view raw or flat)
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:
> SELECT a.id FROM a, b WHERE a.id = b.id;
> 
>                                        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 a.id FROM a, b WHERE a.id = b.id;
set enable_seqscan=off;
explain analyze SELECT a.id FROM a, b WHERE a.id = b.id;

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

Responses

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-2014 The PostgreSQL Global Development Group