pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PFC <lists@boutiquenumerique.com>
To: josh@agliodbs.com, "Alvaro Herrera" <alvherre@dcc.uchile.cl>
To: tgl@sss.pgh.pa.us
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Functionscan estimates
Date: Sat, 09 Apr 2005 13:25:47 +0200
Message-ID: <op.soyp090rth1vuj@localhost> (raw)
In-Reply-To: <200504081604.27845.josh@agliodbs.com>
References: <200504081515.50340.josh@agliodbs.com>
	<200504081604.27845.josh@agliodbs.com>
	<20050408224556.GC19458@dcc.uchile.cl>
	<20050408223820.GA17450@winnie.fuhr.org>


> My solution would be a lot simpler, since we could simply populate
> pg_proc.proestrows with "1000" by default if not changed by the DBA.  In  
> an
> even better world, we could tie it to a table, saying that, for example,
> proestrows = my_table*0.02.

	What if the estimated row is a function of a parameter ?
	Say a function takes as a parameter :
	- a number to use in a LIMIT
	- it's a function to generate a certain number of values from a  
predetermined set (like, array -> set returning function)

	In all those cases it's no use to have just a fixed number.

	Id suggest two solutions :
	- The ideal solution which is impossible to do :
	The function tells the planner about its stats, looking at its parameters

	- A solution that would be possible to do
	 pg_proc.proestrows is... the name of another function, defined by the  
user, which takes the exact same parameters as the set returning function  
we're talking about, and which returns estimates.

	For instance, in pseudo-sql :

CREATE FUNCTION int_array_srf( INTEGER[] ) RETURNS SETOF INTEGER LANGUAGE  
plpgsql AS $$
BEGIN
	FOR _i IN 1..icount($1)
		RETURN NEXT $1[_i];
	END
END	In the two cases above, this would give :

CREATE FUNCTION array_srf_estimator( INTEGER[] ) RETURNS INTEGER LANGUAGE  
plpgsql AS $$
BEGIN
	RETURN icount( $1 );
END;

ALTER FUNCTION array_srf SET ESTIMATOR array_srf_estimator;

	Another interesting case would be the famous "Top 5 by category" case  
where we use a SRF to emulate an index skip scan. Say we have a table  
Categories and a table Users, each User having columns "categories" and  
"score" and we want the N users with best score in each category :

CREATE FUNCTION top_n_by_category( INTEGER ) RETURN SETOF users%ROWTYPE  
LANGUAGE plpgsql AS $$
DECLARE
	_cat_id	INTEGER;
	_n ALIAS FOR $1;
	_user	users%ROWTYPE;
BEGIN
	FOR _cat_id IN SELECT category_id FROM categories DO
		FOR _user IN SELECT * FROM users WHERE category_id = _cat_id ORDER BY  
score DESC LIMIT _n DO
			RETURN NEXT _user;
		END
	END
END
	
CREATE FUNCTION top_n_by_category_estimator( INTEGER ) RETURN INTEGER  
LANGUAGE plpgsql AS $$
BEGIN
	RETURN $1 * (the estimated number of rows for the categories table taken  
 from the table statistics);
END;

ALTER FUNCTION top_n_by_category SET ESTIMATOR top_n_by_category_estimator;

	Got it ?

	The array_srf case would be extremely useful as this type of function is  
generally used to join against other tables, and having estimates is  
useful for that.
	The top_n case would be useless if we're just returning the rows from the  
function directly, but would be useful if we'll join them to other tables.

	This sounds pretty simple, powerful, and versatile.

	Additionally, in some cases (most notably the array case) it's easy to  
estimate the statistics on the returned values because they're all in the  
array already, so the mechanism could be extended to have a way of  
returning a pseudo pg_stats for a Set Returning function.

	For instance, say you have a SRF which returns N random rows from a  
table. It could have an estimator which would return a rowcount of N, and  
a statistics estimator which would return the sats rows for the source  
table, appropriately modified.

	This sounds harder to do.

	WHat do you think ?




















view thread (13+ messages)  latest in thread

Message-ID: <op.soyp090rth1vuj@localhost>
Permalink:  ../op.soyp090rth1vuj@localhost/
Also on:    postgresql.org/message-id/op.soyp090rth1vuj@localhost

 · 

reply

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-performance@postgresql.org
  Cc: lists@boutiquenumerique.com, alvherre@dcc.uchile.cl, tgl@sss.pgh.pa.us, mike@fuhr.org
  Subject: Re: Functionscan estimates
  In-Reply-To: <op.soyp090rth1vuj@localhost>

* 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