seq scan instead of index scan

[Karl Larsson <karl.larsson47@xxxxxxxxx>]

Hello.

I have a problem I don't understand. I hope it's a simple problem and I'm
just stupid.

When I make a subquery Postgres don't care about my indexes and makes
a seq scan instead of a index scan. Why?

Is it possible that the subquery change the datatype and by this make
a index scan impossible? Can I somehow see the datatypes used by the
query?

Below is the test I'm running.

/ Karl Larsson


CREATE TABLE table_one (
    id bigint PRIMARY KEY NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"table_one_pkey" for table "table_one"

CREATE TABLE table_two (

    id bigint PRIMARY KEY NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"table_two_pkey" for table "table_two"




INSERT INTO table_one VALUES (4);

INSERT INTO table_one VALUES (3);
INSERT INTO table_one VALUES (5);
INSERT INTO table_one VALUES (2);
INSERT INTO table_one VALUES (6);
INSERT INTO table_one VALUES (1);

INSERT INTO table_two VALUES (14);

INSERT INTO table_two VALUES (12);
INSERT INTO table_two VALUES (10);
INSERT INTO table_two VALUES (8);
INSERT INTO table_two VALUES (6);
INSERT INTO table_two VALUES (4);
INSERT INTO table_two VALUES (2);



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
-------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=35.44..78.58 rows=647 width=8) (actual time=0.076..0.088 rows=2 loops=1)
   Hash Cond: (t2.id = t1.id)
   ->  Seq Scan on table_two t2  (cost=0.00..29.40 rows=1940 width=8) (actual time=0.007..0.021 rows=7 loops=1)
   ->  Hash  (cost=27.35..27.35 rows=647 width=8) (actual time=0.038..0.038 rows=5 loops=1)
         ->  Bitmap Heap Scan on table_one t1  (cost=9.26..27.35 rows=647 width=8) (actual time=0.014..0.022 rows=5 loops=1)
               Recheck Cond: (id < 6)
               ->  Bitmap Index Scan on table_one_pkey  (cost=0.00..9.10 rows=647 width=0) (actual time=0.008..0.008 rows=5 loops=1)
                     Index Cond: (id < 6)
 Total runtime: 0.133 ms