Skip site navigation (1) Skip section navigation (2)

Performance of IN (...) vs. = ANY array[...]

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 (view raw or flat)
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

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-09-15 20:19:21
Subject: Re: Performance of IN (...) vs. = ANY array[...]
Previous:From: Bucky JordanDate: 2006-09-15 18:28:02
Subject: Re: RAID 0 not as fast as expected

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group