From: russm <russm@icorp.com.au>
To: pgsql-sql@postgresql.org
Subject: alternate idioms for large "IN (...)" lists
Date: Sun, 02 Jun 2002 16:10:02 +1000
Message-ID: <B91FF3DA.40C6%russm@icorp.com.au> (raw)
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
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-sql@postgresql.org
Cc: russm@icorp.com.au
Subject: Re: alternate idioms for large "IN (...)" lists
In-Reply-To: <B91FF3DA.40C6%russm@icorp.com.au>
* 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