pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect with grouping plan question
2+ messages / 2 participants
[nested] [flat]
* Select with grouping plan question
@ 2005-11-30 19:21 Brad Might <bmight@storediq.com>
2005-11-30 20:41 ` Re: Select with grouping plan question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Brad Might @ 2005-11-30 19:21 UTC (permalink / raw)
To: pgsql-performance
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
I thought that the idx_vq_entities_1 index would allow an ordered scan
of the table. I created it based pon the sort key given in the explain
statement.
Thanks in advance.
Table "data_schema.volume_queue_entities"
Column | Type | Modifiers
-----------------+-------------------+----------------------------------
-----------------+-------------------+-------------
vq_record_id | bigint | default
currval('seq_vq_fsmd_auto'::regclass)
vq_entity_type | character varying |
vq_entity_value | character varying |
Indexes:
"idx_vq_entities_1" btree (vq_record_id, vq_entity_type,
vq_entity_value)
Table "volume_8.entity_type_id_mapping"
Column | Type | Modifiers
-------------+-------------------+--------------------------------------
-------------+-------------------+--------------------
entity_id | integer | default
nextval('volume_8.entity_id_sequence'::regclass)
entity_type | character varying |
explain insert into volume_8.volume_node_entity_data_values
(vs_volume_id, vs_latest_node_synthetic_id, vs_base_entity_id, vs_value,
vs_value_count, vs_base_entity_revision_id)
select 8, vq_record_id, entity_id , vq_entity_value,
count(vq_entity_value),1 from data_schema.volume_queue_entities qe,
volume_8.entity_type_id_mapping emap
where qe.vq_entity_type = emap.entity_type group by
vq_record_id, vq_entity_type, vq_entity_value, entity_id ;
------------------------------------------------------------------------
----------------------------------------
Subquery Scan "*SELECT*" (cost=184879640.90..210689876.26
rows=543373376 width=60)
-> GroupAggregate (cost=184879640.90..199822408.74 rows=543373376
width=37)
-> Sort (cost=184879640.90..186238074.34 rows=543373376
width=37)
Sort Key: qe.vq_record_id, qe.vq_entity_type,
qe.vq_entity_value, emap.entity_id
-> Hash Join (cost=1.70..18234833.10 rows=543373376
width=37)
Hash Cond: (("outer".vq_entity_type)::text =
("inner".entity_type)::text)
-> Seq Scan on volume_queue_entities qe
(cost=0.00..10084230.76 rows=543373376 width=33)
-> Hash (cost=1.56..1.56 rows=56 width=16)
-> Seq Scan on entity_type_id_mapping emap
(cost=0.00..1.56 rows=56 width=16)
(9 rows)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Select with grouping plan question
2005-11-30 19:21 Select with grouping plan question Brad Might <bmight@storediq.com>
@ 2005-11-30 20:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-11-30 20:41 UTC (permalink / raw)
To: Brad Might <bmight@storediq.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-11-30 20:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-30 19:21 Select with grouping plan question Brad Might <bmight@storediq.com>
2005-11-30 20:41 ` Tom Lane <tgl@sss.pgh.pa.us>
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