pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTrivial function query optimized badly
5+ messages / 3 participants
[nested] [flat]
* Trivial function query optimized badly
@ 2007-01-04 03:11 Craig A. James <cjames@modgraph-usa.com>
0 siblings, 2 replies; 5+ messages in thread
From: Craig A. James @ 2007-01-04 03:11 UTC (permalink / raw)
To: pgsql-performance
Well, once again I'm hosed because there's no way to tell the optimizer the cost for a user-defined function. I know this issue has already been raised (by me!) several times, but I have to remind everyone about this. I frequently must rewrite my SQL to work around this problem.
Here is the function definition:
CREATE OR REPLACE FUNCTION cansmiles(text) RETURNS text
AS '/usr/local/pgsql/lib/libchem.so', 'cansmiles'
LANGUAGE 'C' STRICT IMMUTABLE;
Here is the bad optimization:
db=> explain analyze select version_id, 'Brc1ccc2nc(cn2c1)C(=O)O' from version where version.isosmiles = cansmiles('Brc1ccc2nc(cn2c1)C(=O)O', 1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on version (cost=0.00..23.41 rows=1 width=4) (actual time=1434.281..1540.253 rows=1 loops=1)
Filter: (isosmiles = cansmiles('Brc1ccc2nc(cn2c1)C(=O)O'::text, 1))
Total runtime: 1540.347 ms
(3 rows)
I've had to break it up into two separate queries. Ironically, for large databases, Postgres does the right thing -- it computes the function, then uses the index on the "isosmiles" column. It's blazingly fast and very satisfactory. But for small databases, it apparently decides to recompute the function once per row, making the query N times slower (N = number of rows) than it should be!
In this instance, there are 1000 rows, and factor of 10^4 is a pretty dramatic slowdown... To make it work, I had to call the function separately then use its result to do the select.
db=> explain analyze select cansmiles('Brc1ccc2nc(cn2c1)C(=O)O', 1);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=1.692..1.694 rows=1 loops=1)
Total runtime: 1.720 ms
(2 rows)
db=> explain analyze select version_id, 'Brc1ccc2nc(cn2c1)C(=O)O' from version where version.isosmiles = 'Brc1ccc2nc(cn2c1)C(=O)O';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using i_version_isosmiles on version (cost=0.00..5.80 rows=1 width=4) (actual time=0.114..0.117 rows=1 loops=1)
Index Cond: (isosmiles = 'Brc1ccc2nc(cn2c1)C(=O)O'::text)
Total runtime: 0.158 ms
(3 rows)
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trivial function query optimized badly
@ 2007-01-04 04:17 Adam Rich <adam.r@sbcglobal.net>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 5+ messages in thread
From: Adam Rich @ 2007-01-04 04:17 UTC (permalink / raw)
To: 'Craig A. James' <cjames@modgraph-usa.com>; pgsql-performance
Craig,
What version of postgres are you using? I just tested this on PG 8.1.2
and was unable to reproduce these results. I wrote a simple function
that returns the same text passed to it, after sleeping for 1 second.
I use it in a where clause, like your example below, and regardless of
the number of rows in the table, it still takes roughly 1 second,
indicating to me the function is only called once.
Is it possible that your function really isn't immutable? Would PG
realize this and fall back to treating it as VOLATILE ?
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Craig A.
James
Sent: Wednesday, January 03, 2007 9:11 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Trivial function query optimized badly
Well, once again I'm hosed because there's no way to tell the optimizer
the cost for a user-defined function. I know this issue has already
been raised (by me!) several times, but I have to remind everyone about
this. I frequently must rewrite my SQL to work around this problem.
Here is the function definition:
CREATE OR REPLACE FUNCTION cansmiles(text) RETURNS text
AS '/usr/local/pgsql/lib/libchem.so', 'cansmiles'
LANGUAGE 'C' STRICT IMMUTABLE;
Here is the bad optimization:
db=> explain analyze select version_id, 'Brc1ccc2nc(cn2c1)C(=O)O' from
version where version.isosmiles = cansmiles('Brc1ccc2nc(cn2c1)C(=O)O',
1);
QUERY PLAN
------------------------------------------------------------------------
--------------------------------
Seq Scan on version (cost=0.00..23.41 rows=1 width=4) (actual
time=1434.281..1540.253 rows=1 loops=1)
Filter: (isosmiles = cansmiles('Brc1ccc2nc(cn2c1)C(=O)O'::text, 1))
Total runtime: 1540.347 ms
(3 rows)
I've had to break it up into two separate queries. Ironically, for
large databases, Postgres does the right thing -- it computes the
function, then uses the index on the "isosmiles" column. It's blazingly
fast and very satisfactory. But for small databases, it apparently
decides to recompute the function once per row, making the query N times
slower (N = number of rows) than it should be!
In this instance, there are 1000 rows, and factor of 10^4 is a pretty
dramatic slowdown... To make it work, I had to call the function
separately then use its result to do the select.
db=> explain analyze select cansmiles('Brc1ccc2nc(cn2c1)C(=O)O', 1);
QUERY PLAN
------------------------------------------------------------------------
------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=1.692..1.694
rows=1 loops=1)
Total runtime: 1.720 ms
(2 rows)
db=> explain analyze select version_id, 'Brc1ccc2nc(cn2c1)C(=O)O' from
version where version.isosmiles = 'Brc1ccc2nc(cn2c1)C(=O)O';
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------
Index Scan using i_version_isosmiles on version (cost=0.00..5.80
rows=1 width=4) (actual time=0.114..0.117 rows=1 loops=1)
Index Cond: (isosmiles = 'Brc1ccc2nc(cn2c1)C(=O)O'::text)
Total runtime: 0.158 ms
(3 rows)
Craig
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trivial function query optimized badly
@ 2007-01-04 05:42 Craig A. James <cjames@modgraph-usa.com>
parent: Adam Rich <adam.r@sbcglobal.net>
0 siblings, 0 replies; 5+ messages in thread
From: Craig A. James @ 2007-01-04 05:42 UTC (permalink / raw)
To: Adam Rich <adam.r@sbcglobal.net>; +Cc: pgsql-performance
Adam Rich wrote:
> Craig,
> What version of postgres are you using? I just tested this on PG 8.1.2
> and was unable to reproduce these results. I wrote a simple function
> that returns the same text passed to it, after sleeping for 1 second.
> I use it in a where clause, like your example below, and regardless of
> the number of rows in the table, it still takes roughly 1 second,
> indicating to me the function is only called once.
Sorry, I forgot that critical piece of info: I'm using 8.1.4.
Your results would indicate that 8.1.2 creates a different plan than 8.1.4, or else there's some configuration parameter that's different between your installation and mine that causes a radically different plan to be used. I assume you vacuum/analyzed the table before you ran the query.
> Is it possible that your function really isn't immutable? Would PG
> realize this and fall back to treating it as VOLATILE ?
Now that you say this, this seems more like a bug with the definition of IMMUTABLE. The function should only be called once if it's given a constant string, right? So the fact that Postgres called it once per row is just wrong.
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trivial function query optimized badly
@ 2007-01-04 05:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2007-01-04 05:46 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> CREATE OR REPLACE FUNCTION cansmiles(text) RETURNS text
> AS '/usr/local/pgsql/lib/libchem.so', 'cansmiles'
> LANGUAGE 'C' STRICT IMMUTABLE;
Umm ... this is a single-argument function.
> db=> explain analyze select version_id, 'Brc1ccc2nc(cn2c1)C(=O)O' from version where version.isosmiles = cansmiles('Brc1ccc2nc(cn2c1)C(=O)O', 1);
And this query is invoking some other, two-argument function; which
apparently hasn't been marked IMMUTABLE, else it'd have been folded
to a constant.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trivial function query optimized badly
@ 2007-01-04 06:57 Craig A. James <cjames@modgraph-usa.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Craig A. James @ 2007-01-04 06:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> "Craig A. James" <cjames@modgraph-usa.com> writes:
>> CREATE OR REPLACE FUNCTION cansmiles(text) RETURNS text
>> AS '/usr/local/pgsql/lib/libchem.so', 'cansmiles'
>> LANGUAGE 'C' STRICT IMMUTABLE;
>
> Umm ... this is a single-argument function.
>
>> db=> explain analyze select version_id, 'Brc1ccc2nc(cn2c1)C(=O)O' from version where version.isosmiles = cansmiles('Brc1ccc2nc(cn2c1)C(=O)O', 1);
>
> And this query is invoking some other, two-argument function; which
> apparently hasn't been marked IMMUTABLE, else it'd have been folded
> to a constant.
Good catch, mystery solved. There are two definitions for this function, the first just a "wrapper" for the second with the latter parameter defaulting to "1". The second definition was missing the "IMMUTABLE" keyword.
Thanks!
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-01-04 06:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-04 03:11 Trivial function query optimized badly Craig A. James <cjames@modgraph-usa.com>
2007-01-04 04:17 ` Adam Rich <adam.r@sbcglobal.net>
2007-01-04 05:42 ` Craig A. James <cjames@modgraph-usa.com>
2007-01-04 05:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-04 06:57 ` Craig A. James <cjames@modgraph-usa.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