From: Benjamin Minshall <minshall@intellicon.biz>
To: pgsql-performance@postgresql.org
Subject: Performance of IN (...) vs. = ANY array[...]
Date: Fri, 15 Sep 2006 15:12:07 -0400
Message-ID: <450AFB07.5050600@intellicon.biz> (raw)
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.bizAttachments:
smime.p7s (application/x-pkcs7-signature, 5.2K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: minshall@intellicon.biz
Subject: Re: Performance of IN (...) vs. = ANY array[...]
In-Reply-To: <450AFB07.5050600@intellicon.biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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