Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 8C960475935
	for <pgsql-sql@postgresql.org>; Sun,  2 Jun 2002 02:10:30 -0400 (EDT)
Received: from mail.icorp.com.au (mail.icorp.com.au [203.43.143.200])
	by postgresql.org (Postfix) with ESMTP id 4FC5C475476
	for <pgsql-sql@postgresql.org>; Sun,  2 Jun 2002 02:10:27 -0400 (EDT)
Received: from [144.132.96.33] by mail.icorp.com.au
	with SMTP (QuickMail Pro Server for Mac 2.1); 02-Jun-2002 16:10:13 +1000
User-Agent: Microsoft-Entourage/10.0.0.1309
Date: Sun, 02 Jun 2002 16:10:02 +1000
Subject: alternate idioms for large "IN (...)" lists
From: russm <russm@icorp.com.au>
To: <pgsql-sql@postgresql.org>
Message-ID: <B91FF3DA.40C6%russm@icorp.com.au>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit
X-Archive-Number: 200206/7
X-Sequence-Number: 7972

I'm working on an application (originally written for Oracle) that is being
brought to it's knees by one commonly-executed query - finding the most
common attributes for a given list of assets...


SELECT attribute_id, COUNT(asset_id)
  FROM attribute_map
 WHERE asset_id IN ( <potentially several thousand asset_id values> )
 GROUP BY attribute_id
HAVING COUNT(asset_id) < <cutoff value>
 ORDER BY count DESC
 LIMIT <limit>


where attribute_map is a many-to-many map of asset_id to attribute_id -

demo=# \d attribute_map
  Table "attribute_map"
   Column     |  Type   | Modifiers
--------------+---------+-----------
 asset_id     | integer |
 attribute_id | integer |
Indexes: am_asset_id_idx,
         am_attribute_id_idx
Unique keys: am_asset_id_attribute_id_un
Triggers: RI_ConstraintTrigger_26912,
          RI_ConstraintTrigger_26906