Postgres Pro
Downloads
Home   >   mailing lists

Thread: Want to use my own query-plan

Want to use my own query-plan

From
dakotali kasap
Date:
<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 />

Re: Want to use my own query-plan

From
Neil Conway
Date:
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

Re: Want to use my own query-plan

From
dakotali kasap
Date:
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

----- 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

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


Re: Want to use my own query-plan

From
Alvaro Herrera
Date:
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.