Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux