[HACKERS] enforcing a plan (in brief)
Is there a way to bypass the optimizer and to specify a plan to be executed?
Something like:
Limit
-> Nested Loop
-> Nested Loop
-> Seq Scan on tab00 t00
-> Index Scan using tab03_pkey on tab03 t03
Index Cond: ("outer".id = t03.id)
-> Index Scan using tab01_pkey on tab01 t01
Index Cond: ("outer".id = t01.id)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] enforcing a plan
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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] modifying a TupleTableSlot
If I have a TupleTableSlot, and I need to obtain another TupleTableSlot with a different TupleDesc (same fields but different order). Is there a way to do it without that I go myself and try to retrieve the fields and form the tuple myself? In other words, are there functions already in postgres that I can use together to reach this? Regards, --h ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] planner & target lists
Hi, I am confused about an internal point of the planner. Consider a select query and the output target list at the root of the tree. This target lists points to some Vars. Each of which has as relation either INNER/OUTER. Does this INNER/OUTER refer to the inner/outer relations of the top-most node in the tree or to the bottom-most one. In other words, in the following tree, a variable in B that shows in Op1's target list, does it have its relation as INNER (which is B) or OUTER (which is Op2) Op1 / \ / \ / \ Op2 Op3 / \ / \ / \ / \ A B C D Regards, --h ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] printing HeapTuple
Is there an existing function that I can use to print the "HeapTuple" return value of "ExecScanHashBucket" in nodeHash.c? Thanks, --h ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pulling projection up in plans
To be more specific, i am trying to implement an operator, and i get the tuples being projected in ExecScan (called from ExecSeqScan). I just needed them unprojected. thanks, --h On Mon, 16 Aug 2004, Sailesh Krishnamurthy wrote: > >>>>> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes: > > Tom> "Hicham G. Elmongui" <[EMAIL PROTECTED]> writes: > >> is there a way to pull the projection operator to the top of > >> the query plan? I wish there's a variable that can be set to do > >> so. > > Tom> Could you be more specific about what you're hoping to > Tom> accomplish? > > This is just a suspicion, but I suspect that Hicham wants to avoid > tuples being projected in Scan operators .. in a select (*) query > projecting a tuple essentially causes a copy from the buffer pool to > the backend process' heap space. I guess it would work just fine to > have the tuple remain in the buffer and keep the buffer pinned. > > (In TelegraphCQ we actually do this .. we also don't really > materialize join tuples - instead we have an "intermediate tuple > format" which is a list of pointers to the various source tuples. This > makes sense as we do shared query processing - ie. different queries > with similar join predicates and different projection attributes use > the same physical join tuples - projection is the final operation on > the intermediate tuples when we return the tuples to clients. We did > this really to facilitate sharing and don't really have any hard > numbers on whether this would make sense in a general context with > pgsql. Actually IIRC we did consider this - if this would make a diff > to pgsql - and did some crude perf studies and found that it didn't > really help if there was no sharing .. as is the case with pgsql). > > -- > Pip-pip > Sailesh > http://www.cs.berkeley.edu/~sailesh > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] pulling projection up in plans
Hi, is there a way to pull the projection operator to the top of the query plan? I wish there's a variable that can be set to do so. Thanks, --h ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] am i asking in the wrong place?
Hi everybody, I never meant my emails to be spam. That's why i am just asking whether my questions here are out of subject. Typically my questions are about postgresql source code, like the question below. Please advise me whether i should forward my questions to somewhere else. Thanks a lot, --h On Thu, 5 Aug 2004, Hicham G. Elmongui wrote: > In "join_selectivity" function (plancat.c), a function call is made to > "OidFunctionCall4" (fmgr.c), which in turn calls a function pointer. > > In need to know what is the actual function being called from > OidFunctionCall4 if the selectivity of mergejoin is the one required from > join_selectivity. > > Thanks > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] error in linking
Hi, i am trying to add some functions to postgresql. I created a file called "./src/backend/executor/testing.c" then created a file called "./src/include/executor/testing.h" then added "testing.o" to the OBJS in "./src/backend/executor/Makefile" I included "executor/testing.h" in execMain.c, and from there, I tried to make a function call to function which is in testing.c When I tried to gmake, i got the following error message: Undefined first referenced symbol in file PrintPlan executor/SUBSYS.o PrintQuery executor/SUBSYS.o ld: fatal: Symbol referencing errors. No output written to postgres this is an error during the linking. Any idea what i am missing? thanks, --h ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] OidFunctionCall4
In "join_selectivity" function (plancat.c), a function call is made to "OidFunctionCall4" (fmgr.c), which in turn calls a function pointer. In need to know what is the actual function being called from OidFunctionCall4 if the selectivity of mergejoin is the one required from join_selectivity. Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] enforcing a join type
Maybe I didn't make myself clear enough. I didn't have a problem with postgresql. I am just playing around with the code, tracing some parts in order to understand the code well. This is just an experiment with the code. That's why I posted it to hackers. Please let me know if this is still the wrong place for this question. --h -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 6:42 PM To: Hicham G. Elmongui Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [HACKERS] enforcing a join type As this is not really a hacking issue, I'm moving it out of hackers and into general. Please post all replies there not in hackers. Anyway, I'm afraid I'd have to ask WHY you're trying to just disable it? Is the query planner making the wrong decision with good statistics, or are you getting bad statistics? Can you post an explain analyze of the query(s) that are making you want to make this change? Just turning off a join method isn't the way to fix PostgreSQL, getting it to pick the right one is. On Wed, 2004-08-04 at 17:26, Hicham G. Elmongui wrote: > I didn't mean about doing this from a front end. I want to disable > nested_loop and hash_join from the backend. > I tried to set the variables (enable_nestloop and enable_hashjoin) in > costsize.c, but this didn't do it. > Thanks, > --h > > > > > -Original Message- > From: Scott Marlowe [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 04, 2004 5:41 PM > To: Hicham G. Elmongui > Cc: [EMAIL PROTECTED] > Subject: Re: [HACKERS] enforcing a join type > > On Wed, 2004-08-04 at 14:53, Hicham G. Elmongui wrote: > > Hi, > > If I want the planner/optimizer to always choose merge join when it needs > to > > join relations. How can I do it ? > > >From my past experience, I'd guess what you're really trying to do is > STOP the planner from choosing a nested_loop join, in which case it's > quite easy: > > set enable_nestloop = off; > select * from ... > > Of course, you could apply the same basic trick to all other join > methods, and postgresql would then favor using the merge join. > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] enforcing a join type
I didn't mean about doing this from a front end. I want to disable nested_loop and hash_join from the backend. I tried to set the variables (enable_nestloop and enable_hashjoin) in costsize.c, but this didn't do it. Thanks, --h -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 5:41 PM To: Hicham G. Elmongui Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] enforcing a join type On Wed, 2004-08-04 at 14:53, Hicham G. Elmongui wrote: > Hi, > If I want the planner/optimizer to always choose merge join when it needs to > join relations. How can I do it ? >From my past experience, I'd guess what you're really trying to do is STOP the planner from choosing a nested_loop join, in which case it's quite easy: set enable_nestloop = off; select * from ... Of course, you could apply the same basic trick to all other join methods, and postgresql would then favor using the merge join. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] enforcing a join type
Hi, If I want the planner/optimizer to always choose merge join when it needs to join relations. How can I do it ? Thanks, --h ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
