I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index:
sophia=> explain analyze select count(distinct(source_doc)) from public.datapoints where company_id = 'matalan';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=821.43..821.44 rows=1 width=8) (actual time=0.301..0.301 rows=1 loops=1)
-> Index Scan using i_datapoints_cid_sheet_tag_period on datapoints (cost=0.41..820.49 rows=375 width=16) (actual time=0.028..0.171 rows=375 loops=1)
Index Cond: ((company_id)::text = 'matalan'::text)
Planning time: 0.070 ms
Execution time: 0.397 ms
(5 rows)
So far, so good. Nice and simple. Now I wrap this as part of a query against companies - it does exactly the same thing:
sophia=> explain analyze select (select count(distinct(source_doc)) from public.datapoints where company_id = 'matalan') from public.companies c where company_id = 'matalan';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on companies c (cost=821.44..843.19 rows=5 width=8) (actual time=0.333..0.334 rows=1 loops=1)
Filter: ((company_id)::text = 'matalan'::text)
Rows Removed by Filter: 2
InitPlan 1 (returns $0)
-> Aggregate (cost=821.43..821.44 rows=1 width=8) (actual time=0.322..0.322 rows=1 loops=1)
-> Index Scan using i_datapoints_cid_sheet_tag_period on datapoints (cost=0.41..820.49 rows=375 width=16) (actual time=0.017..0.172 rows=375 loops=1)
Index Cond: ((company_id)::text = 'matalan'::text)
Planning time: 0.162 ms
Execution time: 0.409 ms
(9 rows)
sophia=>
Same index used, same performance - exactly what I'd expect.
Now, if I change the inner query to use the company id from the outer query, it stops using the index:
sophia=> explain analyze select (select count(distinct(source_doc)) from public.datapoints where company_id = c.company_id) from public.companies c where company_id = 'matalan';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Seq Scan on companies c (cost=0.00..8179.66 rows=5 width=8) (actual time=2.338..2.339 rows=1 loops=1)
Filter: ((company_id)::text = 'matalan'::text)
Rows Removed by Filter: 2
SubPlan 1
-> Aggregate (cost=1631.57..1631.58 rows=1 width=8) (actual time=2.295..2.296 rows=1 loops=1)
-> Seq Scan on datapoints (cost=0.00..1611.97 rows=7839 width=16) (actual time=0.040..2.179 rows=375 loops=1)
Filter: ((company_id)::text = (c.company_id)::text)
Rows Removed by Filter: 15303
Planning time: 0.109 ms
Execution time: 2.367 ms
(10 rows)
It's the same tables, same data, same results but in this case it refuses to use the index. Why? I ran analyze on both tables, same affect.
The table structure is as follows:
sophia=> \d public.datapoints;
Table "public.datapoints"
Column | Type | Collation | Nullable | Default
------------------------+------------------------+-----------+----------+----------------------------------------
id | integer | | not null | nextval('datapoints_id_seq'::regclass)
company_id | character varying(20) | | not null |
tag | character varying(256) | | |
dict_tag_id | integer | | |
tag_source | character varying(10) | | |
table_context | character varying(256) | | |
extracted_label | character varying(256) | | |
contextualized_label | character varying(256) | | |
normalized_label | character varying(256) | | |
normalized_label_model | character varying(256) | | |
column_header | character varying(256) | | |
period | character varying(20) | | not null |
value | numeric | | not null |
rounded_value | numeric | | |
reported_value | numeric | | |
priority | numeric | | | 0
sheet_type | character(2) | | not null |
subcontext | character varying(5) | | |
main_table | boolean | | not null | false
use_for_calculation | boolean | | not null | false
use_for_ui_display | boolean | | not null | false
use_for_ordering | boolean | | not null | false
use_for_comps | boolean | | not null | false
use_for_forecasting | boolean | | not null | false
ignore_deltas | boolean | | not null | false
primary_table | boolean | | not null | false
source_type | character(2) | | not null |
source_doc | character varying(30) | | |
debug_info | jsonb | | not null | '{}'::jsonb
Indexes:
"datapoints_pkey" PRIMARY KEY, btree (id)
"i_datapoints_cid_sheet_tag_period" btree (company_id, sheet_type, tag, period, normalized_label, use_for_calculation)
"i_datapoints_priority" hash (priority)
"i_datapoints_sheet_tag" btree (sheet_type, tag)
"i_datapoints_use_for_calculation" btree (use_for_calculation)
"i_datapoints_use_for_comps" btree (use_for_comps)
Foreign-key constraints:
"datapoints_sheet_type_fkey" FOREIGN KEY (sheet_type) REFERENCES sheet_type(code)
"datapoints_source_type_fkey" FOREIGN KEY (source_type) REFERENCES source_type(code)
"datapoints_subcontext_fkey" FOREIGN KEY (subcontext) REFERENCES subcontexts(code)
"datapoints_tag_source_fkey" FOREIGN KEY (tag_source) REFERENCES tag_sources(tag_source)
For this exercise there are only two companies:
sophia=> select company_id, count(1) from public.datapoints group by 1;
company_id | count
------------+-------
repsol | 15303
matalan | 375
(2 rows)
Using the index would allow it to ignore almost all the rows.
The companies table is trivial (it makes no difference to the problem):
sophia=> \d public.companies;
Table "public.companies"
Column | Type | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
company_id | character varying(20) | | |
For example, the following is equally troubling:
sophia=> explain analyze select (select count(distinct(source_doc)) from public.datapoints where company_id = c.company_id) from (values ('repsol'), ('matalan') ) c(company_id) where company_id in ('matalan', 'repsol');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..3263.20 rows=2 width=8) (actual time=27.922..34.830 rows=2 loops=1)
Filter: (column1 = ANY ('{matalan,repsol}'::text[]))
SubPlan 1
-> Aggregate (cost=1631.57..1631.58 rows=1 width=8) (actual time=17.403..17.403 rows=1 loops=2)
-> Seq Scan on datapoints (cost=0.00..1611.97 rows=7839 width=16) (actual time=0.060..6.525 rows=7839 loops=2)
Filter: ((company_id)::text = "*VALUES*".column1)
Rows Removed by Filter: 7839
Planning time: 0.150 ms
Execution time: 34.886 ms
(9 rows)
sophia=>
Running an explain directly without any analyze immediately after I create the table gives yet another plan although this quickly gets replaced with one that's using a full table scan.
sophia=> explain select (select count(distinct(source_doc)) from public.datapoints where company_id = c.company_id) from public.companies c where company_id = 'matalan';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on companies c (cost=0.00..197.23 rows=5 width=8)
Filter: ((company_id)::text = 'matalan'::text)
SubPlan 1
-> Aggregate (cost=35.09..35.10 rows=1 width=8)
-> Bitmap Heap Scan on datapoints (cost=8.46..35.07 rows=7 width=78)
Recheck Cond: ((company_id)::text = (c.company_id)::text)
-> Bitmap Index Scan on i_datapoints_cid_sheet_tag_period (cost=0.00..8.46 rows=7 width=0)
Index Cond: ((company_id)::text = (c.company_id)::text)
(8 rows)
sophia=> explain select (select count(distinct(source_doc)) from public.datapoints where company_id = c.company_id) from public.companies c where company_id = 'matalan';
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on companies c (cost=0.00..8179.66 rows=5 width=8)
Filter: ((company_id)::text = 'matalan'::text)
SubPlan 1
-> Aggregate (cost=1631.57..1631.58 rows=1 width=8)
-> Seq Scan on datapoints (cost=0.00..1611.97 rows=7839 width=16)
Filter: ((company_id)::text = (c.company_id)::text)
(6 rows)
For the avoidance of doubt, I did nothing else on the database between running the first and second of the two queries above.
If I understood why it was doing a 'Bitmap Index Scan' rather than a sequential or normal index scan that'd hopefully be a good pointer.
If I make the company ID unique, I get the performance I want but of course at this point, I've trashed my data so it's not really a solution:
sophia=> update public.datapoints set company_id= id;
UPDATE 15678
sophia=> explain select (select count(distinct(source_doc)) from public.datapoints where company_id = c.company_id) from public.companies c where company_id = 'matalan';
QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on companies c (cost=0.00..16095.56 rows=5 width=8)
Filter: ((company_id)::text = 'matalan'::text)
SubPlan 1
-> Aggregate (cost=3214.75..3214.76 rows=1 width=8)
-> Seq Scan on datapoints (cost=0.00..3176.14 rows=15446 width=16)
Filter: ((company_id)::text = (c.company_id)::text)
(6 rows)
sophia=> analyze public.datapoints;
ANALYZE
sophia=> explain select (select count(distinct(source_doc)) from public.datapoints where company_id = c.company_id) from public.companies c where company_id = 'matalan';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on companies c (cost=0.00..63.95 rows=5 width=8)
Filter: ((company_id)::text = 'matalan'::text)
SubPlan 1
-> Aggregate (cost=8.43..8.44 rows=1 width=8)
-> Index Scan using i_datapoints_cid_sheet_tag_period on datapoints (cost=0.41..8.43 rows=1 width=16)
Index Cond: ((company_id)::text = (c.company_id)::text)
(6 rows)
sophia=>
This is a simplified version of the real queries and tables I want to run on but it exhibits similar problems (I think - the real queries take so long I can't run them) and I still want to understand what's happening. The real DB has more data, more irrelevant columns and the query performance is a bigger pain.
Any bright ideas much appreciated. I'm guessing this has something to do with the statistics on which it bases the query plans but I'm not sure what.
