I am doing an experiment in which I need the following:
SET enable_mergejoin = false;
SET enable_hashjoin = false;
SELECT ...
FROM tab00 as T00, tab01 as T01, tab02 as T02, tab03 as T03
WHERE T00.id = T01.id
AND T00.id = T02.id
AND T00.id = T03.id
LIMIT 51;
There's an index on each primary key (id). Hence, what I expect and what I
get is the following:
Limit (cost=0.00..913.95 rows=51 width=12) -> Nested Loop (cost=0.00..89620.80 rows=5001 width=12) -> Nested Loop
(cost=0.00..59725.19rows=5001 width=18) -> Nested Loop (cost=0.00..29917.10 rows=5001 width=12) -> Seq Scan
ontab00 t00 (cost=0.00..109.01 rows=5001 width=6) -> Index Scan using tab03_pkey on tab03 t03
(cost=0.00..5.95rows=1 width=6) Index Cond: ("outer".id = t03.id) -> Index Scan using tab01_pkey on
tab01t01 (cost=0.00..5.95 rows=1 width=6) Index Cond: ("outer".id = t01.id) -> Index Scan
usingtab02_pkey on tab02 t02 (cost=0.00..5.95 rows=1 width=6) Index Cond: (t02.id = "outer".id)
I need the sequential scan to be on tab02 instead. What to do? The 4 tables
have the same schema. The data distribution is the same for the 4 tables,
except for one of them (tab02); the rows are clustered in reversed order.
Even if I try to swap the data between tab00 and tab02, I get the same
logical query: the sequential scan on the unwanted table.
Is there a way o enforce a plan definition?
Thanks,
--h