Hello,
I have come across a plan that should never get generated IMHO:
SELECT 1
FROM extdataregular e1
INNER JOIN extdataempty e2 ON e1.field = e2.field AND e1.index = e2.index
generates the following plan:
Nested Loop (cost=1.13..528540.89 rows=607604 width=4) (actual
time=9298.504..9298.506
> I'd say it's a pretty different problem. The cache hit ratio
> discussion on that thread talks about underestimating the hit ratio.
> That particular problem could only lead to Memoize plans *not* being
> chosen when they maybe should be. Not the other way around, which is
> your case.
>
> creat
Hello,
please consider the following SQL query:
SELECT * FROM "transactions" WHERE
"account" IN (SELECT "ID" FROM "accounts" WHERE "name" ~~* '%test%') OR
"contract" IN (SELECT "ID" FROM "contracts" WHERE "name" ~~* '%test%')
This yields the following plan on Postgres 11:
Seq S
rows_from_subplan2
=> rows_total =
((1735088 * (1 - 0.025)) / 80277) * 46 +
((1735088 * (1 - 1)) / 0) * 5
=> rows_total = 969 + 0 /* no non-null values for contract field */
Please forgive the sloppy math but something along this line could be promising.
Btw, I don't quite understand why the nested loop on contract only is expected
to yield 31662 rows, when the null_frac of field transactions.contract is 1.
Shouldn't that indicate zero rows or some kind of default minimum estimate for
that query?
Thanks again!
Benjamin Coutu
> While you're waiting, you might think about recasting the query to
> avoid the OR. Perhaps you could do a UNION of two scans of the
> transactions table?
Thanks for the hint, I am well aware of the workaround for OR via UNION. I am
not trying to improve this query per se as it is the small roo
