Anyone can commet that issue?
More extremal sample (simplified version of what i get in real world situation):
same table data...
Query:
select * from (SELECT table1.id,(select count(*) from table2 where table2.fk=table1.id) as total from table1) as t1 where total=990 or total=991 or total=992 or total=993 or
total=994 or total=995 or total=996 or total=997 or total=998 or total=999 or total=1000 or total=1001 or total=1002 or total=1003 or total=1004 or total=1005 or total=1006 or
total=1007 or total=1008 or total=1009 or total=1010;
But postgres use bad bad plan for that query:
testdb=# EXPLAIN ANALYZE select * from (SELECT table1.id,(select count(*) from table2 where table2.fk=table1.id) as total from table1) as t1 where total=990 or total=991 or
total=992 or total=993 or total=994 or total=995 or total=996 or total=997 or total=998 or total=999 or total=1000 or total=1001 or total=1002 or total=1003 or total=1004 or
total=1005 or total=1006 or total=1007 or total=1008 or total=1009 or total=1010;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on table1 (cost=0.00..906433.96 rows=17 width=4) (actual time=1035.481..15695.443 rows=12 loops=1)
Filter: (((subplan) = 990) OR ((subplan) = 991) OR ((subplan) = 992) OR ((subplan) = 993) OR ((subplan) = 994) OR ((subplan) = 995) OR ((subplan) = 996) OR ((subplan) = 997) OR
((subplan) = 998) OR ((subplan) = 999) OR ((subplan) = 1000) OR ((subplan) = 1001) OR ((subplan) = 1002) OR ((subplan) = 1003) OR ((subplan) = 1004) OR ((subplan) = 1005) OR
((subplan) = 1006) OR ((subplan) = 1007) OR ((subplan) = 1008) OR ((subplan) = 1009) OR ((subplan) = 1010))
SubPlan
-> Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=16.308..16.309 rows=1 loops=39)
-> Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.021..14.839 rows=1000 loops=39)
Filter: (fk = $0)
-> Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=16.286..16.288 rows=1 loops=39)
-> Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.021..14.817 rows=1000 loops=39)
Filter: (fk = $0)
-> Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=16.434..16.436 rows=1 loops=39)
-> Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.021..14.957 rows=1000 loops=39)
Filter: (fk = $0)
........
17 more aggregate seq scans
........
-> Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=16.316..16.317 rows=1 loops=12)
-> Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.020..14.845 rows=1000 loops=12)
Filter: (fk = $0)
Total runtime: 15696.295 ms
(70 rows)
vs right version:
testdb=# EXPLAIN ANALYZE select * from (SELECT table1.id,(select count(*) from table2 where table2.fk=table1.id) as total from table1 offset 0) as t1 where total=990 or total=991
or total=992 or total=993 or total=994 or total=995 or total=996 or total=997 or total=998 or total=999 or total=1000 or total=1001 or total=1002 or total=1003 or total=1004 or
total=1005 or total=1006 or total=1007 or total=1008 or total=1009 or total=1010;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan t1 (cost=0.00..42480.25 rows=17 width=12) (actual time=63.121..804.438 rows=12 loops=1)
Filter: ((t1.total = 990) OR (t1.total = 991) OR (t1.total = 992) OR (t1.total = 993) OR (t1.total = 994) OR (t1.total = 995) OR (t1.total = 996) OR (t1.total = 997) OR
(t1.total = 998) OR (t1.total = 999) OR (t1.total = 1000) OR (t1.total = 1001) OR (t1.total = 1002) OR (t1.total = 1003) OR (t1.total = 1004) OR (t1.total = 1005) OR (t1.total =
1006) OR (t1.total = 1007) OR (t1.total = 1008) OR (t1.total = 1009) OR (t1.total = 1010))
-> Limit (cost=0.00..42477.12 rows=50 width=4) (actual time=15.029..804.190 rows=50 loops=1)
-> Seq Scan on table1 (cost=0.00..42477.12 rows=50 width=4) (actual time=15.027..804.053 rows=50 loops=1)
SubPlan
-> Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=16.072..16.073 rows=1 loops=50)
-> Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.020..14.599 rows=1000 loops=50)
Filter: (fk = $0)
Total runtime: 804.552 ms
(9 rows)
performance difference 20 times... :(((
I think is is just missoptimisation from db side.
PS: in real world query work around view:
CREATE VIEW test_view as SELECT table1.id,(select count(*) from table2 where table2.fk=table1.id) as total from table1;
and i have no way put offset 0 into query
select * from test_view where total=990 or total=991 or total=992 or total=993 or total=994 or total=995 or total=996 or total=997 or total=998 or total=999 or total=1000 or
total=1001 or total=1002 or total=1003 or total=1004 or total=1005 or total=1006 or total=1007 or total=1008 or total=1009 or total=1010;
--
Maxim Boguk
