enforcing a plan

From: "Hicham G(dot) Elmongui" <elmongui(at)cs(dot)purdue(dot)edu>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: enforcing a plan
Date: 2005-02-10 03:25:20
Message-ID: 200502100325.j1A3PKBR025583@newman.cs.purdue.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.19 rows=5001 width=18)
-> Nested Loop (cost=0.00..29917.10 rows=5001 width=12)
-> Seq Scan on tab00 t00 (cost=0.00..109.01 rows=5001 width=6)
-> Index Scan using tab03_pkey on tab03 t03
(cost=0.00..5.95 rows=1 width=6)
Index Cond: ("outer".id = t03.id)
-> Index Scan using tab01_pkey on tab01 t01
(cost=0.00..5.95 rows=1 width=6)
Index Cond: ("outer".id = t01.id)
-> Index Scan using tab02_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

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephan Szabo 2005-02-10 06:06:07 Re: Function .. AS..?
Previous Message David Fetter 2005-02-10 02:33:10 Re: Query optimizer 8.0.1 (and 8.0)