Re: Table with many NULLS for indexed column yields strange query plan

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"