Re: (Mis)using the PostgreSQL planner to get estimated row counts

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