Re: seq scan instead of index scan

[Karl Larsson <karl.larsson47@xxxxxxxxx>]

On Fri, Dec 18, 2009 at 1:10 AM, Greg Smith <greg@xxxxxxxxxxxxxxx> 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.

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