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

Seqscans are not disabled. Also, this is PostgreSQL 10.11 if that helps.

Costs are as follows:

seq_page_cost
---------------
1

random_page_cost
------------------
1.5

It is odd that it does not just do a seqscan on table3. It's a very small
table... only like 36 rows. I'd think the plan *should* seq scan table3,
get the id where number = 'xxxx', then use the index
index_table2_on_table3_id on table2 to get the matching rows for that id.
It does use that index when I specify that table3_id is not null, but not
otherwise.

table3_id is very selective into table2 for any non-null value, so I don't
know why it would choose to scan that entire index in the case of the first
query where the table3_id clearly can't be null due to the inner join.
Check out this:

select tablename, attname, inherited, null_frac, avg_width, n_distinct,
most_common_vals, most_common_freqs from pg_stats where tablename = 'table2'
and attname = 'table3_id';
tablename | attname | inherited | null_frac | avg_width |
n_distinct |
---------------+------------------+-----------+-----------+-----------+------------+
table2 | table3_id | f | 0.996167 |
8 | 39 |

most_common_vals:
{985,363,990,991,992,45,81,8,126,307,378,739,855,993,994,190,338,366,369,537,663,805,846,155,277,803,870,988}
most_common_freqs:
{0.000233333,0.0002,0.0002,0.0002,0.0002,0.000166667,0.000166667,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}

Thanks again for any help.

Greig

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html