Re: nested loop semijoin estimates

Hi,

On 05/30/15 23:16, Tom Lane wrote:
> I wrote:
>> So what this seems to mean is that for SEMI/ANTI join cases, we have to
>> postpone all of the inner scan cost determination to final_cost_nestloop,
>> so that we can do this differently depending on whether
>> has_indexed_join_quals() is true. That's a little bit annoying because it
>> will mean we take the shortcut exit less often; but since SEMI/ANTI joins
>> aren't that common, it's probably not going to be a big planning time hit.
>
> Attached is a draft patch for that. It fixes the problem for me:
>
> Nested Loop Semi Join (cost=0.99..9.09 rows=1 width=74) (actual time=0.591..1.554 rows=2 loops=1)
> -> Index Scan using term_facttablename_columnname_idx on term t (cost=0.55..8.57 rows=1 width=74) (actual time=0.022..0.025 rows=2 loops=1)
> Index Cond: (((facttablename)::text = 'facttable_stat_fta4'::text) AND ((columnname)::text = 'berechnungsart'::text))
> -> Index Only Scan using facttable_stat_fta4_berechnungsart_idx on facttable_stat_fta4 f (cost=0.43..143244.98 rows=5015134 width=2) (actual time=0.759..0.759 rows=1 loops=2)
> Index Cond: (berechnungsart = (t.term)::text)
> Heap Fetches: 0
> Planning time: 0.545 ms
> Execution time: 1.615 ms

Seems to be working OK, but I still do get a Bitmap Heap Scan there (but
more about that later).

Do you plan to push that into 9.5, or 9.6? I assume it's a behavior
change so that no back-patching, right?

>
>> Not sure yet about your other point about the indexscan getting
>> rejected too soon. That doesn't seem to be happening for me, at
>> least not in HEAD.
>
> I do see something of the sort if I turn off enable_indexonlyscan.
> Not sure about a good fix for that aspect. It may not be terribly
> critical, since AFAICS index-only scans generally ought to apply
> in these cases.

Hmmm, a VACUUM FREEZE fixed that for me. The reason is that right after
loading the testcase, I do get this:

Index Only Scan using facttable_stat_fta4_berechnungsart_idx
on facttable_stat_fta4 f (cost=0.43..280220.51 rows=5000016 width=2)

and after VACUUM FREEZE I do get this:

Index Only Scan using facttable_stat_fta4_berechnungsart_idx
on facttable_stat_fta4 f (cost=0.43..142344.43 rows=5000000 width=2)

and the Bitmap Heap Scan case looks like this:

Bitmap Heap Scan on facttable_stat_fta4 f
(cost=93594.56..200342.76 rows=5000016 width=2)

so it's cheaper (total cost) than the index only scan before freezing,
and more expensive than index only scan after freezing.

I still think this is wrong (or rather "suboptimal") - there are
probably cases where even the "freezed" index only scan is more
expensive than a bitmap heap scan, and in that case the it won't be
used, although it'd be much faster.

Another example is a query with a plain index scan, e.g. consider this
slight modification of the query:

SELECT facttablename, columnname, term FROM term t WHERE
facttablename='facttable_stat_fta4' AND columnname='berechnungsart' AND
EXISTS (SELECT 1 FROM facttable_stat_fta4 f WHERE
f.berechnungsart=t.term AND einheit IS NOT NULL);

This will result in bitmap index scan no matter the visibility.

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services