[SQL] (Mis)using the PostgreSQL planner to get estimated row counts
I'm trying to write a PostgreSQL extension to estimate the number of
rows returned by a SELECT statement. Ideally, it'd be invoked along
the lines of "SELECT estimate_row_count('SELECT foo FROM bar INNER
JOIN baz ON (id) WHERE a=b');", and would be useful for estimating
the number of pages in a search result, for example.
I've got as far as figuring out how to get an estimated row count
from a Node object for a query (mostly by looking at the code for
EXPLAIN), but there I'm a bit mired - I can't figure out how to take
a string representing an SQL statement and parse it into a Node
object I can feed to the planner.
So, a couple of questions:
1) Can anyone suggest where I should look in the source for the
requisite functions for parsing an SQL string into a Node I can feed
to the planner so I can get an estimated row count?
2) Is this something that's reasonable to do in the first place? I'm
not sure if extensions are supposed to be allowed to delve into
PostgreSQL's internals this much.
3) Are there any other gotchas around this area? For example, there's
a lot going on with Snapshots and ActiveSnapshot that I really don't
have any idea about.
-Nick Johnson
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] (Mis)using the PostgreSQL planner to get estimated row counts
I'm trying to write a PostgreSQL extension to estimate the number of
rows returned by a SELECT statement. Ideally, it'd be invoked along the
lines of "SELECT estimate_row_count('SELECT foo FROM bar INNER JOIN baz
ON (id) WHERE a=b');", and would be useful for estimating the number of
pages in a search result, for example.
I've got as far as figuring out how to get an estimated row count from a
Node object for a query (mostly by looking at the code for EXPLAIN), but
there I'm a bit mired - I can't figure out how to take a string
representing an SQL statement and parse it into a Node object I can feed
to the planner.
So, a couple of questions:
1) Can anyone suggest where I should look in the source for the
requisite functions for parsing an SQL string into a Node I can feed to
the planner so I can get an estimated row count?
2) Is this something that's reasonable to do in the first place? I'm not
sure if extensions are supposed to be allowed to delve into PostgreSQL's
internals this much.
3) Are there any other gotchas around this area? For example, there's a
lot going on with Snapshots and ActiveSnapshot that I really don't have
any idea about.
-Nick Johnson
---(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
Re: [SQL] preventing deletion of a row
Kenneth Gonsalves wrote: hi i have a table of the type: id serial unique, name varchar(50), fixed boolean default FALSE if the value of 'fixed' is TRUE, then this row cannot be deleted. How do i enforce this condition? Try this: CREATE RULE tablename_delete AS ON DELETE TO tablename WHERE OLD.fixed = TRUE DO INSTEAD NOTHING; -Nick Johnson ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ENUM like data type
Martín Marqués wrote: I personally think that the ENUM data type is for databases that are not well designed. So, if you see the need for ENUM, that means you need to re-think your data design. You mean like all those instances in the PostgreSQL system catalogs where character(1) has been used as a pseudo-enum of sorts? -Nick Johnson ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
