From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: queries with DISTINCT / GROUP BY giving different plans
Date: Wed, 14 Aug 2013 17:33:53 +0200
Message-ID: <3a56c4d2c0b2daa11a625c2983823901.squirrel@sq.gransy.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi all,
I've run into a strange plan difference on 9.1.9 - the first query does
"DISTINCT" by doing a GROUP BY on the columns (both INT).
SELECT
"f_account"."name_id" AS "a_1550",
"f_message"."text_id" AS "a_1562"
FROM "f_accountmessagefact"
INNER JOIN "f_message" ON ( "f_accountmessagefact"."message_id" =
"f_message"."id" )
INNER JOIN "f_account" ON ( "f_accountmessagefact"."account_id" =
"f_account"."id" )
GROUP BY 1, 2;
QUERY PLAN
-----------------------------------------------------------------------------------------
Group (cost=3575011.59..3721066.43 rows=19473978 width=8)
-> Sort (cost=3575011.59..3623696.54 rows=19473978 width=8)
Sort Key: f_account.name_id, f_message.text_id
-> Hash Join (cost=51718.44..1217195.39 rows=19473978 width=8)
Hash Cond: (f_accountmessagefact.account_id = f_account.id)
-> Hash Join (cost=51699.42..949409.18 rows=19473978
width=8)
Hash Cond: (f_accountmessagefact.message_id =
f_message.id)
-> Seq Scan on f_accountmessagefact
(cost=0.00..435202.78 rows=19473978 width=8)
-> Hash (cost=37002.52..37002.52 rows=1175752 width=8)
-> Seq Scan on f_message (cost=0.00..37002.52
rows=1175752 width=8)
-> Hash (cost=11.23..11.23 rows=623 width=8)
-> Seq Scan on f_account (cost=0.00..11.23 rows=623
width=8)
(12 rows)
Now, this takes ~45 seconds to execute, but after rewriting the query to
use the regular DISTINCT it suddenly switches to HashAggregate with ~1/3
the cost (although it produces the same output, AFAIK), and it executes in
~15 seconds.
SELECT DISTINCT
"f_account"."name_id" AS "a_1550",
"f_message"."text_id" AS "a_1562"
FROM "f_accountmessagefact"
INNER JOIN "f_message" ON ( "f_accountmessagefact"."message_id" =
"f_message"."id" )
INNER JOIN "f_account" ON ( "f_accountmessagefact"."account_id" =
"f_account"."id" );
QUERY PLAN
------------------------------------------------------------------------------------------
HashAggregate (cost=1314565.28..1509305.06 rows=19473978 width=8)
-> Hash Join (cost=51718.44..1217195.39 rows=19473978 width=8)
Hash Cond: (f_accountmessagefact.account_id = f_account.id)
-> Hash Join (cost=51699.42..949409.18 rows=19473978 width=8)
Hash Cond: (f_accountmessagefact.message_id = f_message.id)
-> Seq Scan on f_accountmessagefact (cost=0.00..435202.78
rows=19473978 width=8)
-> Hash (cost=37002.52..37002.52 rows=1175752 width=8)
-> Seq Scan on f_message (cost=0.00..37002.52
rows=1175752 width=8)
-> Hash (cost=11.23..11.23 rows=623 width=8)
-> Seq Scan on f_account (cost=0.00..11.23 rows=623 width=8)
(10 rows)
I've tested this with other queries and those actually behave as expected
(both using HashAggregate), so I'm wondering what's wrong with this one
and why it's discarding a plan with much lower cost. Any ideas?
The estimates are quite exact (and exactly the same for both queries).
BTW I can't test this on 9.2 or 9.3 easily, as this is our production
environment and I can't just export the data. I've tried to simulate this
but so far no luck.
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: tv@fuzzy.cz
Subject: Re: queries with DISTINCT / GROUP BY giving different plans
In-Reply-To: <3a56c4d2c0b2daa11a625c2983823901.squirrel@sq.gransy.com>
* 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