Обсуждение: Want to use my own query-plan
<div style="font-family:times new roman, new york, times, serif;font-size:12pt">Hi everybody,<br /><br />I have started towork on a project that will be implemented on top of Postgresql. Therefore, I have to warm up with postgresql's internals.I downloaded the source code and currently looking at it. But I have some questions?<br /><br />1. How can I preparemy own query plan? (I will need this because sometimes I can prefer using an index scan of a table or merge-join oftwo relations. Now I just want to give my own simple query plan.)<br /><br />2. How can I make postgresql execute my ownquery plan?<br /><br />Thanks for the answers.<br /><br />Baran<br /></div><br />
On Fri, 2006-10-20 at 16:05 -0700, dakotali kasap wrote: > 1. How can I prepare my own query plan? You can't: there is currently no public API for constructing plans by hand. You could kludge something up by hand, but it would be pretty fragile (internal planner data structures may well change between releases). > 2. How can I make postgresql execute my own query plan? Once you have a valid Plan, you can just feed it to the executor as normal (CreateQueryDesc(), ExecutorStart(), ExecutorRun(), ExecutorEnd(), etc.) -Neil
Actually, the system that we want to build up will keep incomplete information with a decomposition based approach. So a normal relation R will be represented on 3 different relations (T:template, C: components, W:worlds). According to this representation a normal selection with constant on R will be rewritten into a group of SQL statements such that we will get new (T,C,W) triples. At this point, PostgreSQL cannot know what we are doing and the structure of the decomposed relations (I mean doing a merge-join at one step is always the most efficient...). Therefore, I want to say the join-method that will be used or which relation will be outer which one will be inner. (I cannot do this just using SELECT * FROM a JOIN b).
Baran
Baran
----- Original Message ----
From: Neil Conway <neilc@samurai.com>
To: dakotali kasap <dakotalidavid@yahoo.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Sent: Saturday, October 21, 2006 3:10:52 AM
Subject: Re: [HACKERS] Want to use my own query-plan
From: Neil Conway <neilc@samurai.com>
To: dakotali kasap <dakotalidavid@yahoo.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Sent: Saturday, October 21, 2006 3:10:52 AM
Subject: Re: [HACKERS] Want to use my own query-plan
On Fri, 2006-10-20 at 16:05 -0700, dakotali kasap wrote:
> 1. How can I prepare my own query plan?
You can't: there is currently no public API for constructing plans by
hand. You could kludge something up by hand, but it would be pretty
fragile (internal planner data structures may well change between
releases).
> 2. How can I make postgresql execute my own query plan?
Once you have a valid Plan, you can just feed it to the executor as
normal (CreateQueryDesc(), ExecutorStart(), ExecutorRun(),
ExecutorEnd(), etc.)
-Neil
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
> 1. How can I prepare my own query plan?
You can't: there is currently no public API for constructing plans by
hand. You could kludge something up by hand, but it would be pretty
fragile (internal planner data structures may well change between
releases).
> 2. How can I make postgresql execute my own query plan?
Once you have a valid Plan, you can just feed it to the executor as
normal (CreateQueryDesc(), ExecutorStart(), ExecutorRun(),
ExecutorEnd(), etc.)
-Neil
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
dakotali kasap wrote: > Therefore, I want to say the join-method that > will be used or which relation will be outer which one will be inner. > (I cannot do this just using SELECT * FROM a JOIN b). You can't force the system to use a particular join method, but you can select which one is inner and which one is outer -- you just need to set join_collapse_limit to 1, and write the query in the specific order you want. See http://momjian.us/main/writings/pgsql/sgml/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER and http://momjian.us/main/writings/pgsql/sgml/explicit-joins.html (sorry, pointers to development-version docs). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.