Error in Computing Rows and Filtered Rows with Nested Loops & Index Scan+condition

[Krysztophe]

This one comes from a customer (ticket 17151#c151956)

There is already #388 on a similar subject.

This may be a duplicate of #635.

• Description : pev2 computes erroneously Rows and Rows removed by Filters when an Index Scan is used many times through a Nested Loops.

Example: https://explain.dalibo.com/plan/ee7b47deb5e7c2c8#plan/node/3 (json version, same problem)

• Rows = 0 although the index finds 400'000 lines
• the Filter clause removes 60% of lines and not 99%

I understand that it is tricky to compute the real numbers, as returned and filtered values seem averaged among the loops:

   ->  Index Scan using b_aid_d_idx on pg_temp.b (actual time=0.000..0.000 rows=0 loops=1000000)
         Output: b.aid, b.d, b.c
         Index Cond: ((b.aid = (a.aid + 0)) AND (b.d < '2012-01-02 00:00:00'::timestamp without time zone))
         Filter: (b.c > 6)
         Rows Removed by Filter: 1   <---- should be 0.6 (average)

But maybe remove the information or put a :grey_question: if it cannot be computed correctly. It can lead to wrong choices.

Everything works well with (loops=1) with the different plans below, except the last one shown above.

• Full script : pev2_index_non_trouve_vs_filter.sql.txt