From: Nick Johnson <arachnid@notdot.net>
To: pgsql-sql@postgresql.org
Subject: (Mis)using the PostgreSQL planner to get estimated row counts
Date: Wed, 25 May 2005 19:13:25 +1200
Message-ID: <FD3B7D7D-18B1-443D-B229-C235CC7BF44E@notdot.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: arachnid@notdot.net
Subject: Re: (Mis)using the PostgreSQL planner to get estimated row counts
In-Reply-To: <FD3B7D7D-18B1-443D-B229-C235CC7BF44E@notdot.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox