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
