From: | Benjamin Minshall <minshall(at)intellicon(dot)biz> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Performance of IN (...) vs. = ANY array[...] |
Date: | 2006-09-15 19:12:07 |
Message-ID: | 450AFB07.5050600@intellicon.biz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)intellicon(dot)biz>
Senior Developer -- Intellicon, Inc.
http://www.intellicon.biz
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-09-15 20:19:21 | Re: Performance of IN (...) vs. = ANY array[...] |
Previous Message | Bucky Jordan | 2006-09-15 18:28:02 | Re: RAID 0 not as fast as expected |