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
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
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;
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?
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
When I change
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)