pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect count(distinct ...) is slower than select distinct in about 5x
8+ messages / 3 participants
[nested] [flat]
* select count(distinct ...) is slower than select distinct in about 5x
@ 2013-12-06 02:10 jacket41142 <jacket41142@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: jacket41142 @ 2013-12-06 02:10 UTC (permalink / raw)
To: pgsql-performance
Hi
I'm just trying about PostgreSQL, I create a database "test" with a table
"t1":
test=> \d t1
Table "public.t1"
Column | Type | Modifiers
---------+---------+-----------------------------------------------------
col_id | integer | not null default nextval('t1_col_id_seq'::regclass)
col_int | integer |
Indexes:
"t1_pkey" PRIMARY KEY, btree (col_id)
"t1_col_int_idx" btree (col_int)
There are 10001000 rows in that table, and basically col_int of each row
are filled with random data within range [0,1024].
One strange thing I found is that:
test=> select distinct col_int from t1;
Time: 1258.627 ms
test=> select distinct col_int from t1;
Time: 1264.667 ms
test=> select distinct col_int from t1;
Time: 1261.805 ms
If I use "group by":
test=> select distinct col_int from t1 group by col_int;
Time: 1180.617 ms
test=> select distinct col_int from t1 group by col_int;
Time: 1179.849 ms
test=> select distinct col_int from t1 group by col_int;
Time: 1177.936 ms
So the performance difference is not very large.
But when I do that:
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 row)
Time: 7367.476 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 row)
Time: 6946.233 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 row)
Time: 7386.969 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 row)
The speed is straightly worse! But it's doesn't make sense. Since we can
just make a temporary table or subquery and count for all rows. The
performance should be almost the same.
So do you have any idea about this? Or maybe PostgreSQL's query planner can
be improved for this kinds of query?
By the way, if I use a subquery to replace the above query, here's what I
got:
test=> select count(*) from (select distinct col_int from t1) as tmp;
count
-------
1025
(1 row)
Time: 1267.468 ms
test=> select count(*) from (select distinct col_int from t1) as tmp;
count
-------
1025
(1 row)
Time: 1257.327 ms
test=> select count(*) from (select distinct col_int from t1) as tmp;
count
-------
1025
(1 row)
Time: 1258.189 ms
OK, this workaround works. But I just think if postgres can improve with
this kind of query, it will be better. Also I'm not sure about other
similar scenarios that will cause similar problem.
The following is the output of "explain analyze ...":
test=> explain analyze select distinct col_int from t1;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=169268.05..169278.30 rows=1025 width=4) (actual
time=39034.653..39037.239 rows=1025 loops=1)
-> Seq Scan on t1 (cost=0.00..144265.04 rows=10001204 width=4) (actual
time=0.041..19619.931 rows=10001000 loops=1)
Total runtime: 39039.136 ms
(3 rows)
Time: 39103.622 ms
test=> explain analyze select distinct col_int from t1 group by col_int;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=169280.86..169291.11 rows=1025 width=4) (actual
time=39062.417..39064.882 rows=1025 loops=1)
-> HashAggregate (cost=169268.05..169278.30 rows=1025 width=4) (actual
time=39058.136..39060.303 rows=1025 loops=1)
-> Seq Scan on t1 (cost=0.00..144265.04 rows=10001204 width=4)
(actual time=0.024..19439.482 rows=10001000 loops=1)
Total runtime: 39066.896 ms
(4 rows)
Time: 39067.198 ms
test=> explain analyze select count(distinct col_int) from t1;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=169268.05..169268.06 rows=1 width=4) (actual
time=45994.120..45994.123 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..144265.04 rows=10001204 width=4) (actual
time=0.025..19599.950 rows=10001000 loops=1)
Total runtime: 45994.154 ms
(3 rows)
Time: 45994.419 ms
test=> explain analyze select count(*) from (select distinct col_int from
t1) as tmp;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=169291.11..169291.12 rows=1 width=0) (actual
time=39050.598..39050.600 rows=1 loops=1)
-> HashAggregate (cost=169268.05..169278.30 rows=1025 width=4) (actual
time=39046.814..39048.742 rows=1025 loops=1)
-> Seq Scan on t1 (cost=0.00..144265.04 rows=10001204 width=4)
(actual time=0.035..19616.631 rows=10001000 loops=1)
Total runtime: 39050.634 ms
(4 rows)
Time: 39050.896 ms
P.S. I have already use "analyze verbose t1;" several times so the database
should already be optimized. But I'm just new to PostgreSQL.
The environment I use is:
PostgreSQL 9.3.1 (postgresql-9.3 9.3.1-1.pgdg12.4+1) on local machine
(actually a vbox VM, but when I try the test, I didn't run something very
different or some heavy program for all queries. And the result seems
consistent.)
Ubuntu 12.04 LTS
I followed the installation step in Quickstart section of
http://wiki.postgresql.org/wiki/Apt
best regards,
jacket41142
^ permalink raw reply [nested|flat] 8+ messages in thread
* select count(distinct ...) is slower than select distinct in about 5x
@ 2013-12-10 17:28 jacket41142 <jacket41142@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: jacket41142 @ 2013-12-10 17:28 UTC (permalink / raw)
To: pgsql-performance
Hi
I'm just trying about PostgreSQL, I create a database "test" with a table
"t1":
test=> \d t1
Table "public.t1"
Column | Type | Modifiers
---------+---------+-----------------------------------------------------
col_id | integer | not null default nextval('t1_col_id_seq'::regclass)
col_int | integer |
Indexes:
"t1_pkey" PRIMARY KEY, btree (col_id)
"t1_col_int_idx" btree (col_int)
There are 10001000 rows in that table, and basically col_int of each row
are filled with random data within range [0,1024].
One strange thing I found is that:
test=> select distinct col_int from t1;
Time: 1258.627 ms
test=> select distinct col_int from t1;
Time: 1264.667 ms
test=> select distinct col_int from t1;
Time: 1261.805 ms
If I use "group by":
test=> select distinct col_int from t1 group by col_int;
Time: 1180.617 ms
test=> select distinct col_int from t1 group by col_int;
Time: 1179.849 ms
test=> select distinct col_int from t1 group by col_int;
Time: 1177.936 ms
So the performance difference is not very large.
But when I do that:
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 row)
Time: 7367.476 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 row)
Time: 6946.233 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 row)
Time: 7386.969 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 row)
The speed is straightly worse! But it's doesn't make sense. Since we can
just make a temporary table or subquery and count for all rows. The
performance should be almost the same.
So do you have any idea about this? Or maybe PostgreSQL's query planner can
be improved for this kinds of query?
By the way, if I use a subquery to replace the above query, here's what I
got:
test=> select count(*) from (select distinct col_int from t1) as tmp;
count
-------
1025
(1 row)
Time: 1267.468 ms
test=> select count(*) from (select distinct col_int from t1) as tmp;
count
-------
1025
(1 row)
Time: 1257.327 ms
test=> select count(*) from (select distinct col_int from t1) as tmp;
count
-------
1025
(1 row)
Time: 1258.189 ms
OK, this workaround works. But I just think if postgres can improve with
this kind of query, it will be better. Also I'm not sure about other
similar scenarios that will cause similar problem.
The following is the output of "explain analyze ...":
test=> explain analyze select distinct col_int from t1;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=169268.05..169278.30 rows=1025 width=4) (actual
time=39034.653..39037.239 rows=1025 loops=1)
-> Seq Scan on t1 (cost=0.00..144265.04 rows=10001204 width=4) (actual
time=0.041..19619.931 rows=10001000 loops=1)
Total runtime: 39039.136 ms
(3 rows)
Time: 39103.622 ms
test=> explain analyze select distinct col_int from t1 group by col_int;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=169280.86..169291.11 rows=1025 width=4) (actual
time=39062.417..39064.882 rows=1025 loops=1)
-> HashAggregate (cost=169268.05..169278.30 rows=1025 width=4) (actual
time=39058.136..39060.303 rows=1025 loops=1)
-> Seq Scan on t1 (cost=0.00..144265.04 rows=10001204 width=4)
(actual time=0.024..19439.482 rows=10001000 loops=1)
Total runtime: 39066.896 ms
(4 rows)
Time: 39067.198 ms
test=> explain analyze select count(distinct col_int) from t1;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=169268.05..169268.06 rows=1 width=4) (actual
time=45994.120..45994.123 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..144265.04 rows=10001204 width=4) (actual
time=0.025..19599.950 rows=10001000 loops=1)
Total runtime: 45994.154 ms
(3 rows)
Time: 45994.419 ms
test=> explain analyze select count(*) from (select distinct col_int from
t1) as tmp;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=169291.11..169291.12 rows=1 width=0) (actual
time=39050.598..39050.600 rows=1 loops=1)
-> HashAggregate (cost=169268.05..169278.30 rows=1025 width=4) (actual
time=39046.814..39048.742 rows=1025 loops=1)
-> Seq Scan on t1 (cost=0.00..144265.04 rows=10001204 width=4)
(actual time=0.035..19616.631 rows=10001000 loops=1)
Total runtime: 39050.634 ms
(4 rows)
Time: 39050.896 ms
P.S. I have already use "analyze verbose t1;" several times so the database
should already be optimized. But I'm just new to PostgreSQL.
The environment I use is:
PostgreSQL 9.3.1 (postgresql-9.3 9.3.1-1.pgdg12.4+1) on local machine
(actually a vbox VM, but when I try the test, I didn't run something very
different or some heavy program for all queries. And the result seems
consistent.)
Ubuntu 12.04 LTS
I followed the installation step in Quickstart section of
http://wiki.postgresql.org/wiki/Apt
best regards,
jacket41142
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: select count(distinct ...) is slower than select distinct in about 5x
@ 2013-12-10 18:16 Kevin Grittner <kgrittn@ymail.com>
parent: jacket41142 <jacket41142@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Kevin Grittner @ 2013-12-10 18:16 UTC (permalink / raw)
To: jacket41142 <jacket41142@gmail.com>; pgsql-performance
jacket41142 <jacket41142@gmail.com> wrote:
> [ subject issue in detail ]
Please review this thread:
http://www.postgresql.org/message-id/flat/CAPNY-2Utce-c+kNTwsMCbAk58=9mYeAeViTXT9LO7r1k77jukw@mail.g...
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: select count(distinct ...) is slower than select distinct in about 5x
@ 2013-12-10 18:19 Jeff Janes <jeff.janes@gmail.com>
parent: jacket41142 <jacket41142@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jeff Janes @ 2013-12-10 18:19 UTC (permalink / raw)
To: jacket41142 <jacket41142@gmail.com>; +Cc: pgsql-performance
On Tue, Dec 10, 2013 at 9:28 AM, jacket41142 <jacket41142@gmail.com> wrote:
>
> test=> select distinct col_int from t1 group by col_int;
> Time: 1177.936 ms
>
> So the performance difference is not very large.
> But when I do that:
>
> test=> select count(distinct col_int) from t1;
> count
> -------
> 1025
> (1 row)
>
> Time: 7367.476 ms
>
count(distinct ...) always sorts, rather than using a hash, to do its work.
I don't think that there is any fundamental reason that it could not be
changed to allow it to use hashing, it just hasn't been done yet. It is
complicated by the fact that you can have multiple count() expressions in
the same query which demand sorting/grouping on different columns.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: select count(distinct ...) is slower than select distinct in about 5x
@ 2013-12-11 01:23 jacket41142 <jacket41142@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: jacket41142 @ 2013-12-11 01:23 UTC (permalink / raw)
To: pgsql-performance
Thanks very much.
I think another problem is that the cost estimation isn't good enough to
reflex real cost. Since we can see, from "explain analyze ...",
count(distinct ...) has smallest cost between the others, but since it uses
sorts, the time complexity should be higher especially for large amount of
rows.
Also I think even if we can have multiple count() expressions, the
optimizer should also be able to choose between use sort, HashAggregate or
maybe something like linear aggregate if sorts are not needed or other
methods if exist. Also this may be done as just one job for entire table of
interested columns, or for each column separately.
regards,
jacket41142
2013/12/11 Jeff Janes <jeff.janes@gmail.com>
> On Tue, Dec 10, 2013 at 9:28 AM, jacket41142 <jacket41142@gmail.com>wrote:
>
>
>>
>> test=> select distinct col_int from t1 group by col_int;
>> Time: 1177.936 ms
>>
>> So the performance difference is not very large.
>> But when I do that:
>>
>> test=> select count(distinct col_int) from t1;
>> count
>> -------
>> 1025
>> (1 row)
>>
>> Time: 7367.476 ms
>>
>
>
> count(distinct ...) always sorts, rather than using a hash, to do its
> work. I don't think that there is any fundamental reason that it could not
> be changed to allow it to use hashing, it just hasn't been done yet. It is
> complicated by the fact that you can have multiple count() expressions in
> the same query which demand sorting/grouping on different columns.
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: select count(distinct ...) is slower than select distinct in about 5x
@ 2013-12-11 01:57 jacket41142 <jacket41142@gmail.com>
parent: jacket41142 <jacket41142@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: jacket41142 @ 2013-12-11 01:57 UTC (permalink / raw)
To: pgsql-performance
I have done another experiment for count(*) vs count(distinct ...), on same
table schema but with 10000000 rows now. And for this time, the postgres
version is 9.3.2 (9.3.2-1.pgdg12.4+1).
These two has same resulted query plan with same estimated cost, but
count(*) is straightly fast.
test=> explain analyze select count(*) from t1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=169247.92..169247.93 rows=1 width=0) (actual
time=37775.187..37775.188 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..144247.94 rows=9999994 width=0) (actual
time=0.037..19303.022 rows=10000000 loops=1)
Total runtime: 37775.216 ms
(3 µ§¸ê®Æ¦C)
®É¶¡: 37775.493 ms
test=> explain analyze select count(distinct col_int) from t1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=169247.92..169247.93 rows=1 width=4) (actual
time=45883.192..45883.195 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..144247.94 rows=9999994 width=4) (actual
time=0.037..19652.540 rows=10000000 loops=1)
Total runtime: 45883.224 ms
(3 µ§¸ê®Æ¦C)
®É¶¡: 45883.473 ms
test=> select count(*) from t1;
count
----------
10000000
(1 µ§¸ê®Æ¦C)
®É¶¡: 602.018 ms
test=> select count(*) from t1;
count
----------
10000000
(1 µ§¸ê®Æ¦C)
®É¶¡: 598.291 ms
test=> select count(*) from t1;
count
----------
10000000
(1 µ§¸ê®Æ¦C)
®É¶¡: 592.439 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 µ§¸ê®Æ¦C)
®É¶¡: 10311.788 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 µ§¸ê®Æ¦C)
®É¶¡: 7063.156 ms
test=> select count(distinct col_int) from t1;
count
-------
1025
(1 µ§¸ê®Æ¦C)
®É¶¡: 6899.283 ms
I don't think count(*) also uses sort since it should not be needed. But
for the query planner, it seems it can not distinguish between these two
now.
regards,
jacket41142
2013/12/11 jacket41142 <jacket41142@gmail.com>
> Thanks very much.
>
> I think another problem is that the cost estimation isn't good enough to
> reflex real cost. Since we can see, from "explain analyze ...",
> count(distinct ...) has smallest cost between the others, but since it uses
> sorts, the time complexity should be higher especially for large amount of
> rows.
>
> Also I think even if we can have multiple count() expressions, the
> optimizer should also be able to choose between use sort, HashAggregate or
> maybe something like linear aggregate if sorts are not needed or other
> methods if exist. Also this may be done as just one job for entire table of
> interested columns, or for each column separately.
>
> regards,
> jacket41142
>
>
> 2013/12/11 Jeff Janes <jeff.janes@gmail.com>
>
>> On Tue, Dec 10, 2013 at 9:28 AM, jacket41142 <jacket41142@gmail.com>wrote:
>>
>>
>>>
>>> test=> select distinct col_int from t1 group by col_int;
>>> Time: 1177.936 ms
>>>
>>> So the performance difference is not very large.
>>> But when I do that:
>>>
>>> test=> select count(distinct col_int) from t1;
>>> count
>>> -------
>>> 1025
>>> (1 row)
>>>
>>> Time: 7367.476 ms
>>>
>>
>>
>> count(distinct ...) always sorts, rather than using a hash, to do its
>> work. I don't think that there is any fundamental reason that it could not
>> be changed to allow it to use hashing, it just hasn't been done yet. It is
>> complicated by the fact that you can have multiple count() expressions in
>> the same query which demand sorting/grouping on different columns.
>>
>> Cheers,
>>
>> Jeff
>>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: select count(distinct ...) is slower than select distinct in about 5x
@ 2013-12-11 02:24 Jeff Janes <jeff.janes@gmail.com>
parent: jacket41142 <jacket41142@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jeff Janes @ 2013-12-11 02:24 UTC (permalink / raw)
To: jacket41142 <jacket41142@gmail.com>; +Cc: pgsql-performance
On Tuesday, December 10, 2013, jacket41142 wrote:
> Thanks very much.
>
> I think another problem is that the cost estimation isn't good enough to
> reflex real cost. Since we can see, from "explain analyze ...",
> count(distinct ...) has smallest cost between the others, but since it uses
> sorts, the time complexity should be higher especially for large amount of
> rows.
>
That one is easy to explain. The cost estimate is not intended to be an
absolute estimate, it is just an relative estimate to choose between
alternatives. Since the current implementation of count(distinct ...)
does not *have* any alternatives for that step in the process, there is no
point in estimating a cost for it. So part of providing it with
alternatives will have to be providing those cost estimates as well.
> Also I think even if we can have multiple count() expressions, the
> optimizer should also be able to choose between use sort, HashAggregate or
> maybe something like linear aggregate if sorts are not needed or other
> methods if exist. Also this may be done as just one job for entire table of
> interested columns, or for each column separately.
>
Right. I hope this gets fixed. It's been on my todo list for a while, but
at the current rate of going through my todo list, it will takes a few
decades to get to if it is left up to me....
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: select count(distinct ...) is slower than select distinct in about 5x
@ 2013-12-12 03:22 jacket41142 <jacket41142@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: jacket41142 @ 2013-12-12 03:22 UTC (permalink / raw)
To: pgsql-performance
2013/12/11 Jeff Janes <jeff.janes@gmail.com>
> On Tuesday, December 10, 2013, jacket41142 wrote:
>
>> Thanks very much.
>>
>> I think another problem is that the cost estimation isn't good enough to
>> reflex real cost. Since we can see, from "explain analyze ...",
>> count(distinct ...) has smallest cost between the others, but since it uses
>> sorts, the time complexity should be higher especially for large amount of
>> rows.
>>
>
> That one is easy to explain. The cost estimate is not intended to be an
> absolute estimate, it is just an relative estimate to choose between
> alternatives. Since the current implementation of count(distinct ...)
> does not *have* any alternatives for that step in the process, there is no
> point in estimating a cost for it. So part of providing it with
> alternatives will have to be providing those cost estimates as well.
>
I got it. Thanks very much for explain.
>
>> Also I think even if we can have multiple count() expressions, the
>> optimizer should also be able to choose between use sort, HashAggregate or
>> maybe something like linear aggregate if sorts are not needed or other
>> methods if exist. Also this may be done as just one job for entire table of
>> interested columns, or for each column separately.
>>
>
> Right. I hope this gets fixed. It's been on my todo list for a while,
> but at the current rate of going through my todo list, it will takes a few
> decades to get to if it is left up to me....
>
Thanks very much for your effort. Also it's still good to know for me that
this problem will be fixed in future. :)
And so until now, if someone want to use count(distinct ...), he can use a
workaround like subquery if performance is a concern. (Of course, he also
needs to take care about NULL values as mentioned in
http://www.postgresql.org/message-id/flat/CAPNY-2Utce-c+kNTwsMCbAk58=9mYeAeViTXT9LO7r1k77jukw@mail.g...
)
best regards,
jacket41142
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2013-12-12 03:22 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-12-06 02:10 select count(distinct ...) is slower than select distinct in about 5x jacket41142 <jacket41142@gmail.com>
2013-12-10 17:28 select count(distinct ...) is slower than select distinct in about 5x jacket41142 <jacket41142@gmail.com>
2013-12-10 18:16 ` Kevin Grittner <kgrittn@ymail.com>
2013-12-10 18:19 ` Jeff Janes <jeff.janes@gmail.com>
2013-12-11 01:23 Re: select count(distinct ...) is slower than select distinct in about 5x jacket41142 <jacket41142@gmail.com>
2013-12-11 01:57 ` jacket41142 <jacket41142@gmail.com>
2013-12-11 02:24 ` Jeff Janes <jeff.janes@gmail.com>
2013-12-12 03:22 ` jacket41142 <jacket41142@gmail.com>
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