> > no, there is strange estimation > > -> Seq Scan on public.x2 (cost=0.00..345560.00 rows=500 > width=4) (actual time=17.914..9330.645 rows=133 loops=1) > Output: x2.a > Filter: (NOT (SubPlan 2)) > Rows Removed by Filter: 867 > SubPlan 2 > -> CTE Scan on pl pl_1 (cost=0.00..468.59 > rows=89000 width=4) (actual time=0.023..8.379 rows=566 loops=1000) > Output: foo(pl_1.a) > > CTE Scan expect rows=89000 > > I don't know how is possible to take too high number >
respective why estimation is unstrable first (1MB work_mem) -> CTE Scan on pl pl_1 (cost=0.00..468.59 rows=89000 width=4) (actual time=0.023..8.379 rows=566 loops=1000) Output: foo(pl_1.a) second (3MB work_mem) -> Hash (cost=1.78..1.78 rows=89 width=4) (actual time=9.650..9.650 rows=89 loops=1) Output: pl.a Buckets: 1024 Batches: 1 Memory Usage: 3kB -> CTE Scan on pl (cost=0.00..1.78 rows=89 width=4) (actual time=8.468..9.346 rows=89 loops=1) Output: pl.a I expect so estimation not depends on work_mem Best regards Pavel > Regards > > Pavel > >> >> If you add "ROWS 10" or so to the declaration of the function, you >> get a better row estimate and it goes back to the hashed subplan. >> >> regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers