We have some complex query that was very slow. I managed to reduce the query to a simple reproduction. It seems that the combination of greatest
and log
is the cause, but I don't understand why.
Here is a full sql-fiddle example to run the queries - and you can also View the execution Plans
of the queries (press the link at the bottom of the query result on the sql-fiddle page)
So here is the slow query:
select count(value)
from (
SELECT log(greatest(1e-9, x)) as value
from (select generate_series(1, 20000, 1) as x) as d
) t;
We just generate a series of 20k numbers and use log(greatest())
. This query takes about 1.5 seconds.
I thought that calculating the log may take long, but the following query is also fast (~5ms):
select count(value)
from (
SELECT log(x) as value
from (select generate_series(1, 20000, 1) as x) as d
) t;
Just as a test I exchanged greatest
and log
- this is also fast (~5ms):
select count(value)
from (
SELECT greatest(1e-9, log(x)) as value
from (select generate_series(1, 20000, 1) as x) as d
) t;
The QUERY PLANS
for all 3 queries are the same:
Aggregate (cost=22.51..22.52 rows=1 width=8)
-> Result (cost=0.00..5.01 rows=1000 width=4)
Can anyone explain why the first query is so slow - and maybe anyone knows a workaround?
More details
slow platforms
I get similar results on all of these (first query is a magnitude slower):
- SQL Fiddle uses pg 9.6
- my local PC with similar results: Win10 64bit, pg 11.5 running in Docker
- remote server: Ubuntu 18.04 64-bit running pg 11.5 in Docker
- rextester.com
- slow query ~ 3sec
- fast query ~ 0.5sec
count
When I change count(value)
to count(*)
or count(1)
(number one) the query is fast
- but this does not help me because the production query does not even include a count
- anyway, I wonder why there is a difference in this case (there are no null-values in the data)
count(1)
is actually slower thancount(*)
in Postgres