Skip site navigation (1) Skip section navigation (2)

Re: low performance on functions returning setof record

From: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Sabin Coanda" <sabin(dot)coanda(at)deuromedia(dot)ro>,pgsql-performance(at)postgresql(dot)org
Subject: Re: low performance on functions returning setof record
Date: 2008-10-09 19:54:47
Message-ID: 59671E7D-367A-42CC-BF1B-E103BE264B27@hi-media.com (view raw or flat)
Thread:
Lists: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Le 9 oct. 08 à 21:30, Tom Lane a écrit :
> There's not a lot you can do about that at the moment.  8.4 will have
> the ability to inline functions returning sets, if they're SQL- 
> language
> and consist of just a single SELECT, but existing releases won't do  
> it.


I'm actually using 8.3 functions cost/rows planner estimation to trick  
it into avoiding nestloop into some INNER JOIN situations where any  
amount of up-to-date statistics won't help.

Will the 8.4 ability to inline plain SQL still consider the given  
hardcoded ROWS estimation?

FWIW the difference of timing of one of the queries where I'm using  
this trick is about 35 mins or more against 48 seconds. It allows the  
planner to choose MergeJoin paths instead of Nestloop ones, where  
inner loop has several millions records, and definitely not just  
several records, like planner/stats bet.

Regards,
- --
dim
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkjuYYcACgkQlBXRlnbh1bmAjgCePkyl9qWTpQ1Gdk/yp3IINK+z
g8EAoJuAzu9B3GUiPI1J5dCcbzeiSABG
=5J6b
-----END PGP SIGNATURE-----

In response to

pgsql-performance by date

Next:From: Carlos MorenoDate: 2008-10-09 23:51:26
Subject: "Mysterious" issues with newly installed 8.3
Previous:From: Tom LaneDate: 2008-10-09 19:30:51
Subject: Re: low performance on functions returning setof record

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group