Re: Different plan for very similar queries

On 2015-05-29 10:55:44 +0200, Peter J. Holzer wrote:
> wdsah=> explain analyze select facttablename, columnname, term, concept_id, t.hidden, language, register
>         from term t where facttablename='facttable_stat_fta4' and columnname='einheit' and exists (select 1 from
facttable_stat_fta4f where f.einheit=t.term ); 
>                                                                                QUERY PLAN
                                                   
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Nested Loop Semi Join  (cost=0.00..384860.48 rows=1 width=81) (actual time=0.061..0.119 rows=2 loops=1)
>    ->  Index Scan using term_facttablename_columnname_idx on term t  (cost=0.00..391.46 rows=636 width=81) (actual
time=0.028..0.030rows=3 loops=1) 
>          Index Cond: (((facttablename)::text = 'facttable_stat_fta4'::text) AND ((columnname)::text =
'einheit'::text))
>    ->  Index Scan using facttable_stat_fta4_einheit_idx on facttable_stat_fta4 f  (cost=0.00..384457.80 rows=21788970
width=3)(actual time=0.027..0.027 rows=1 loops=3) 
>          Index Cond: ((einheit)::text = (t.term)::text)
>  Total runtime: 0.173 ms
> (6 rows)
>
[...]
> wdsah=> explain analyze select facttablename, columnname, term, concept_id, t.hidden, language, register
>         from term t where facttablename='facttable_stat_fta4' and columnname='berechnungsart' and exists (select 1
fromfacttable_stat_fta4 f where f.berechnungsart=t.term ); 
>                                                                                          QUERY PLAN
                                                                      
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Merge Semi Join  (cost=316864.57..319975.79 rows=1 width=81) (actual time=7703.917..30948.271 rows=2 loops=1)
>    Merge Cond: ((t.term)::text = (f.berechnungsart)::text)
>    ->  Index Scan using term_term_idx on term t  (cost=0.00..319880.73 rows=636 width=81) (actual
time=7703.809..7703.938rows=3 loops=1) 
>          Filter: (((facttablename)::text = 'facttable_stat_fta4'::text) AND ((columnname)::text =
'berechnungsart'::text))
>    ->  Index Scan using facttable_stat_fta4_berechnungsart_idx on facttable_stat_fta4 f  (cost=0.00..2545748.85
rows=43577940width=2) (actual time=0.089..16263.582 rows=21336180 loops=1) 
>  Total runtime: 30948.648 ms
> (6 rows)

A couple of additional observations:

The total cost of both queries is quite similar, so random variations
might push into one direction or the other. Indeed, after dropping and
recreating indexes (I tried GIN indexes as suggested by Heikki on [1])
and calling analyze after each change, I have now reached a state where
both queries use the fast plan.

In the first case the query planner seems to add the cost of the two
index scans to get the total cost, despite the fact that for a semi join
the second index scan can be aborted after the first hit (so either the
cost of the second scan should be a lot less than 384457.80 or it needs
to be divided by a large factor for the semi join).

In the second case the cost of the second index scan (2545748.85) is
either completely ignored or divided by a large factor: It doesn't seem
to contribute much to the total cost.

    hp


[1] http://hlinnaka.iki.fi/2014/03/28/gin-as-a-substitute-for-bitmap-indexes/

--
   _  | Peter J. Holzer    | I want to forget all about both belts and
|_|_) |                    | suspenders; instead, I want to buy pants
| |   | hjp@hjp.at         | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/