From: Tom Lane <tgl@sss.pgh.pa.us>
To: Brad Might <bmight@storediq.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Select with grouping plan question
Date: Wed, 30 Nov 2005 15:41:22 -0500
Message-ID: <815.1133383282@sss.pgh.pa.us> (raw)
In-Reply-To: <E387E2E9622FDD408359F98BF183879E222A6A@dc1.storediq.com>
References: <E387E2E9622FDD408359F98BF183879E222A6A@dc1.storediq.com>
"Brad Might" <bmight@storediq.com> writes:
> This seems to me to be an expensive plan and I'm wondering if there's a
> way to improve it or a better way to do what I'm trying to do here (get
> a count of distinct values for each record_id and map that value to the
> entity type) entity_type_id_mapping is 56 rows
> volume_node_entity_data_values is approx 500,000,000 rows vq_record_id
> has approx 11,000,000 different values vq_entity_type is a value in
> entity_type_id_mapping.entity_type
Hmm, what Postgres version is that? And have you ANALYZEd
entity_type_id_mapping lately? I'd expect the planner to realize that
there cannot be more than 56 output groups, which ought to lead it to
prefer a hashed aggregate over the sort+group method. That's what I
get in a test case with a similar query structure, anyway.
If you're stuck on an old PG version, it might help to do the
aggregation first and then join, ie
select ... from
(select count(vq_entity_value) as vcount, vq_entity_type
from data_schema.volume_queue_entities group by vq_entity_type) qe,
volume_8.entity_type_id_mapping emap
where qe.vq_entity_type = emap.entity_type;
regards, tom lane
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: tgl@sss.pgh.pa.us, bmight@storediq.com
Subject: Re: Select with grouping plan question
In-Reply-To: <815.1133383282@sss.pgh.pa.us>
* 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