pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlow performance on functions returning setof record
3+ messages / 3 participants
[nested] [flat]
* low performance on functions returning setof record
@ 2008-10-09 14:47 Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 3+ messages in thread
From: Sabin Coanda @ 2008-10-09 14:47 UTC (permalink / raw)
To: pgsql-performance
Hi there,
I use different functions returning setof record, and they are working well.
The problem is the performance when I use those functions in joins, for
instance:
SELECT *
FROM "Table1" t1
JOIN "Function1"( a1, a2, ... aN ) AS f1( ColA int4, ColB
varchar, ... )
ON t1.ColX = f1.ColA
The problem is I'm not able to make indexes on the function, even inside I
have just another select statement from different permanent tables, with
some where clauses depending on the function arguments.
Do you know a way to build such a function, returning something I can join
in an outer select statement like above, using indexes or another way to run
it faster ?
TIA,
Sabin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: low performance on functions returning setof record
@ 2008-10-09 19:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2008-10-09 19:30 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
"Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
> I use different functions returning setof record, and they are working well.
> The problem is the performance when I use those functions in joins, for
> instance:
> SELECT *
> FROM "Table1" t1
> JOIN "Function1"( a1, a2, ... aN ) AS f1( ColA int4, ColB
> varchar, ... )
> ON t1.ColX = f1.ColA
> The problem is I'm not able to make indexes on the function, even inside I
> have just another select statement from different permanent tables, with
> some where clauses depending on the function arguments.
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.
You might consider trying to refactor your stuff to use views ...
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: low performance on functions returning setof record
@ 2008-10-09 19:54 Dimitri Fontaine <dfontaine@hi-media.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Dimitri Fontaine @ 2008-10-09 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sabin Coanda <sabin.coanda@deuromedia.ro>; 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-----
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-10-09 19:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-10-09 14:47 low performance on functions returning setof record Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-10-09 19:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-10-09 19:54 ` Dimitri Fontaine <dfontaine@hi-media.com>
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