pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of IN (...) vs. = ANY array[...]
2+ messages / 2 participants
[nested] [flat]
* Performance of IN (...) vs. = ANY array[...]
@ 2006-09-15 19:12 Benjamin Minshall <minshall@intellicon.biz>
2006-09-15 20:19 ` Re: Performance of IN (...) vs. = ANY array[...] Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Benjamin Minshall @ 2006-09-15 19:12 UTC (permalink / raw)
To: pgsql-performance
Greetings:
I'm running 8.1.4, and have noticed major differences in execution time
for plpgsql functions running queries that differ only in use of an
array such as:
slower_function( vals integer[] )
[query] WHERE id = ANY vals;
faster_function( vals integer[] )
vals_text := array_to_string( vals, ',' )
EXECUTE '[query] WHERE id IN (' || vals_text || ')';
In general, there are about 10 integers in the lookup set on average and
50 max.
What are the advantages or disadvantages of using arrays in this
situation? The = ANY array method makes plpgsql development cleaner,
but seems to really lack performance in certain cases. What do you
recommend as the preferred method?
Thanks for your comments.
--
Benjamin Minshall <minshall@intellicon.biz>
Senior Developer -- Intellicon, Inc.
http://www.intellicon.biz
Attachments:
smime.p7s (application/x-pkcs7-signature, 5.2K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Performance of IN (...) vs. = ANY array[...]
2006-09-15 19:12 Performance of IN (...) vs. = ANY array[...] Benjamin Minshall <minshall@intellicon.biz>
@ 2006-09-15 20:19 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-09-15 20:19 UTC (permalink / raw)
To: Benjamin Minshall <minshall@intellicon.biz>; +Cc: pgsql-performance
Benjamin Minshall <minshall@intellicon.biz> writes:
> What are the advantages or disadvantages of using arrays in this
> situation? The = ANY array method makes plpgsql development cleaner,
> but seems to really lack performance in certain cases.
In existing releases, the form with IN (list-of-scalar-constants)
can be optimized into indexscan(s), but = ANY (array) isn't.
8.2 will treat them equivalently (in fact, it converts IN (...) to
= ANY (ARRAY[...]) !). So depending on your time horizon, you might
wish to stick with whichever is cleaner for your calling code.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-09-15 20:19 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-15 19:12 Performance of IN (...) vs. = ANY array[...] Benjamin Minshall <minshall@intellicon.biz>
2006-09-15 20:19 ` Tom Lane <tgl@sss.pgh.pa.us>
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