From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: weird execution plan
Date: Fri, 12 Sep 2014 14:34:28 -0700 (PDT)
Message-ID: <1410557668457-5818905.post@n5.nabble.com> (raw)
In-Reply-To: <D83E55F5F4D99B4A9B4C4E259E6227CD01EBF0B6@AUX1EXC02.apac.experian.local>
References: <D83E55F5F4D99B4A9B4C4E259E6227CD01EBF04C@AUX1EXC02.apac.experian.local>
<1410489905373-5818733.post@n5.nabble.com>
<D83E55F5F4D99B4A9B4C4E259E6227CD01EBF0B6@AUX1EXC02.apac.experian.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Huang, Suya wrote
> Both queries have been run several times so cache would have same effect
> on both of them? Below is the plan with buffer information.
Not everyone does so its nice to make certain - especially since I'm not all
that familiar with the code involved. But since no one else has answered I
will theorize.
SELECT count(*) FROM ( SELECT DISTINCT col FROM tbl )
vs
SELECT count(DISTINCT col) FROM tbl
The code for "SELECT DISTINCT col" is likely highly efficient because it
works on complete sets of records.
The code for "SELECT count(DISTINCT col)" is at a relative disadvantage
since it must evaluate one row at a time and remember whether it had seen
the same value previously before deciding whether to increment a counter.
With a large number of duplicate rows the process of making the row set
smaller before counting the end result will perform better since fewer rows
must be evaluated in the less efficient count(DISTINCT) expression - the
time saved there more than offset by the fact that you are effectively
passing over that subset of the data a second time.
HashAggregate(1M rows) + Aggregate(200k rows) < Aggregate(1M rows)
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/weird-execution-plan-tp5818730p5818905.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
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: david.g.johnston@gmail.com
Subject: Re: weird execution plan
In-Reply-To: <1410557668457-5818905.post@n5.nabble.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