Re: Postgresql optimisator deoptimise queries sometime... - Mailing list pgsql-general

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
wheretotal=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=1003or 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
totalfrom 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=1001or 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
totalfrom 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
ortotal=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=1000loops=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
ortotal=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=1009or total=1010; 


--
Maxim Boguk