From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nick Johnson <arachnid@notdot.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: (Mis)using the PostgreSQL planner to get estimated row counts
Date: Tue, 31 May 2005 12:00:13 -0400
Message-ID: <12893.1117555213@sss.pgh.pa.us> (raw)
In-Reply-To: <FD3B7D7D-18B1-443D-B229-C235CC7BF44E@notdot.net>
References: <FD3B7D7D-18B1-443D-B229-C235CC7BF44E@notdot.net>
Nick Johnson <arachnid@notdot.net> writes:
> I'm trying to write a PostgreSQL extension to estimate the number of
> rows returned by a SELECT statement.
Instead of fooling around at the C level, why don't you just do an
EXPLAIN and parse out the first row of the result? For instance
regression=# create function estimate_row_count(text) returns text as $$
regression$# declare x record;
regression$# begin
regression$# for x in execute 'EXPLAIN ' || $1 loop
regression$# return substring(x."QUERY PLAN" from 'rows=([0-9]+) width=');
regression$# end loop;
regression$# end$$ language plpgsql strict;
CREATE FUNCTION
regression=# select estimate_row_count('select * from tenk1');
estimate_row_count
--------------------
10000
(1 row)
Of course this is subject to future breakage due to changes in the
output textual format, etc etc, but it's surely less fragile than
anything written in C will be.
Depending on what you want the results for, it might be best to ignore
any top-level LIMIT node.
regards, tom lane
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: tgl@sss.pgh.pa.us, arachnid@notdot.net
Subject: Re: (Mis)using the PostgreSQL planner to get estimated row counts
In-Reply-To: <12893.1117555213@sss.pgh.pa.us>
* 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