Re: Table with many NULLS for indexed column yields strange queryplan

On 2020-03-05 18:08:53 -0700, greigwise wrote:
> I have a query like this:
>
> SELECT  "table1".* FROM "table1"
> INNER JOIN "table2" ON "table2"."table1_id" = "table1"."id"
> INNER JOIN "table3" ON "table3"."id" = "table2"."table3_id"
> WHERE "table3"."number" = 'xxxx'
> AND ("table2"."type") IN ('Standard') ;
>
> table2 has a large number of NULLS in the column table3_id.  There is an
> index on this column.  Here is the result of explain analyze:
>
> Merge Join  (cost=1001.20..4076.67 rows=17278 width=167) (actual time=284.918..300.167 rows=2244 loops=1)
>    Merge Cond: (table2.table3_id = table3.id)
>    ->  Gather Merge  (cost=1000.93..787825.78 rows=621995 width=175) (actual time=5.786..283.269 rows=64397 loops=1)
>          Workers Planned: 4
>          Workers Launched: 4
>          ->  Nested Loop  (cost=0.87..712740.12 rows=155499 width=175) (actual time=0.091..102.708 rows=13107
loops=5)
>                ->  Parallel Index Scan using index_table2_on_table3_id on table2  (cost=0.43..489653.08 rows=155499
width=16)(actual time=0.027..22.327 rows=13107 loops=5) 
>                      Filter: ((type)::text = 'Standard'::text)

It looks like it postgresql is scanning the index here to get the
entries in the right order for the merge join. It's strange that it
thinks this is a good strategy even though it has to visit every row in
the table (no index cond).

How is the selectivity of "type"? Would an index on that column help?

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"