Re: [HACKERS] Speedups - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: [HACKERS] Speedups
Date
Msg-id 199803051635.LAA15086@candle.pha.pa.us
Whole thread Raw
In response to Re: [HACKERS] Speedups  (ocie@paracel.com)
List pgsql-hackers
> I don't think it is so much a question of syntax as it is a question
> of what we do in the backend.  Suppose I create a stored query in SQL.
> We already store the SQL source for this in the database, right?  So
> when it comes time to execute the query, we take this SQL and execute
> it as if the user had entered it directly.  What I am proposing would
> be to basically store the compiled query plan as well.
>
> I do see a couple sticky points:
>
> We would need some information about which variables are to be
> substituted into this query plan, but this should be fairly
> straightforward.
>
> Some querys may not respond well to this, for example, if a table had
> an index on an integer field f1, this would probably be the best way
> to satisfy a select where f1<10.  But if this were in a query as f1<x,
> then a sufficiently high value of x might make this not such a good
> way to run the query.  I haven't looked into this, but I would assume
> that the optimizer relies on the specific values in such cases.

I have thought about this.  If we take a query string, remove all quoted
constants and numeric constants, we can automatically split apart the
query from the parameters.  We can then look up the non-parameter query
in our cache, and if it matches, replace the new contants with the old
and run the query.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [HACKERS] Re: [QUESTIONS] Problems with running v6.3 on DIGITAL UNIX
Next
From: "D. Dante Lorenso"
Date:
Subject: PostgreSQL and DBI/DBD...vs Pg.pm