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

Re: seq scan instead of index scan

From: Karl Larsson <karl(dot)larsson47(at)gmail(dot)com>
To: Greg Smith <greg(at)2ndquadrant(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: seq scan instead of index scan
Date: 2009-12-18 01:10:32
Message-ID: d7650d320912171710u702236d8ib9ef222477222485@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Dec 18, 2009 at 1:10 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> Karl Larsson wrote:
>
>> When I make a subquery Postgres don't care about my indexes and makes
>> a seq scan instead of a index scan. Why?
>>
> Data set is just too small for it to matter.  Watch what happens if I
> continue from what you posted with much bigger tables:
>
> postgres=# truncate table table_one;
> TRUNCATE TABLE
> postgres=# truncate table table_two;
> TRUNCATE TABLE
> postgres=# insert into table_one (select generate_series(1,100000));
> INSERT 0 100000
> postgres=# insert into table_two (select generate_series(1,100000));
> INSERT 0 100000
> postgres=# analyze;
> ANALYZE
> postgres=# EXPLAIN ANALYZE
>
> SELECT t2.id
> FROM table_two AS t2, (
>   SELECT id
>   FROM table_one AS t1
>   WHERE t1.id < 6
>  ) AS foo
> WHERE t2.id = foo.id;
>                                                            QUERY PLAN
>
>  ------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop  (cost=0.00..91.35 rows=10 width=8) (actual time=0.024..0.048
> rows=5 loops=1)
>  ->  Index Scan using table_one_pkey on table_one t1  (cost=0.00..8.44
> rows=10 width=8) (actual time=0.009..0.013 rows=5 loops=1)
>        Index Cond: (id < 6)
>  ->  Index Scan using table_two_pkey on table_two t2  (cost=0.00..8.28
> rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=5)
>        Index Cond: (t2.id = t1.id)
> Total runtime: 0.097 ms
> (6 rows)
>
> There's the index scan on both tables that you were expecting.<http://www.2ndQuadrant.com>
>

True. Thank you. I'll try this on my reel problem as well but I have a gut
feeling it
won't work there since those tables are bigger.

/ Karl Larsson

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-12-18 01:16:47
Subject: Re: seq scan instead of index scan
Previous:From: Scott MarloweDate: 2009-12-18 00:11:19
Subject: Re: seq scan instead of index scan

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