Thread: Table with many NULLS for indexed column yields strange query plan
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) -> Index Scan using table1_pk on table1 (cost=0.44..1.43 rows=1 width=167) (actual time=0.005..0.005 rows=1 loops=65535) Index Cond: (id = table2.table1_id) -> Index Scan using table3_pkey on table3 (cost=0.27..53.40 rows=1 width=8) (actual time=0.041..0.048 rows=1 loops=1) Filter: ((number)::text = 'xxxx'::text) Rows Removed by Filter: 35 Planning time: 0.450 ms Execution time: 310.230 ms You can see the row estimate there is way off on the Parallel Index Scan. I suspect that this is because it's including the rows with null in the selectivity estimate even though the table3_id can't possibly be null here due to the inner join. If I modify the 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') and table3_id is not null; Just adding in table3_id is not null at the end there, I get a much better plan. Nested Loop (cost=1.14..290.04 rows=66 width=167) (actual time=0.058..11.258 rows=2244 loops=1) -> Nested Loop (cost=0.70..64.46 rows=66 width=8) (actual time=0.049..2.873 rows=2244 loops=1) -> Index Scan using table3_pkey on table3 (cost=0.27..53.40 rows=1 width=8) (actual time=0.030..0.035 rows=1 loops=1) Filter: ((number)::text = 'xxxx'::text) Rows Removed by Filter: 35 -> Index Scan using index_table2_on_table3_id on table2 (cost=0.43..11.05 rows=1 width=16) (actual time=0.017..2.102 rows=2244 loops=1) Index Cond: ((table3_id = table3.id) AND (table3_id IS NOT NULL)) Filter: ((type)::text = 'Standard'::text) -> Index Scan using table1_pk on table1 (cost=0.44..3.42 rows=1 width=167) (actual time=0.003..0.003 rows=1 loops=2244) Index Cond: (id = table2. id) Planning time: 0.403 ms Execution time: 11.672 ms Can I do anything statistics wise so that I get a better plan here or do I have to modify the query. It seems kinda hacky that I would have to specify is not null on that column since like I said it can't possibly be null. Thanks, Greig Wise -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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!"
Attachment
"Peter J. Holzer" <hjp-pgsql@hjp.at> writes: > How is the selectivity of "type"? Would an index on that column help? The EXPLAIN results say that the "type = 'Standard'" condition is completely not selective: in both plans, there is no "Rows Removed by Filter" indication where it's applied, indicating that it did not filter out any rows. Which is odd, because if that isn't removing any rows, why is the planner overestimating the number of rows retrieved from table2 by circa 10x? Maybe a lack of stats for the "type" column? I also find it odd that the second plan is uselessly using an index for table3 (I say "useless" because there's no index condition and no apparent need for the result to be sorted). I suspect the OP has been putting his thumb on the scales in ways he hasn't bothered to tell us about, like fooling with the cost parameters and/or disabling seqscans. regards, tom lane
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