0

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.

5
  • 2
    Can you provide the database structures? An index with "some other stuff" is not very relational. – Gerard H. Pille Sep 4 '20 at 18:28
  • Good point. I've updated the question. – Richard Wheeldon Sep 6 '20 at 11:46
  • Please add table structure for the second table too – NikitaSerbskiy Sep 6 '20 at 11:58
  • and ouput of this query: SELECT company_id, COUNT(1) FROM ublic.datapoints GROUP BY company_id ORDER BY COUNT(1) DESC LIMIT 10; – NikitaSerbskiy Sep 6 '20 at 12:08
  • Updated the question again. The second table makes no material difference AFAICT. – Richard Wheeldon Sep 6 '20 at 12:41
0

What if you run EXPLAIN without ANALYZE? I'm not an expert in PostgreSQL but what if it doesn't push predicate value to the subquery when it builds execution plan? This way it may misestimate the amount of rows returned from datapoints table because it supposes that company_id can be any and in average you have ~8000 of rows per company_id. You have no covered index and it may require a lot of lookups to get the source_doc value so server decides to scan the table. Try to create index on (company_id, source_doc) or test your query on table with more even data distribution.

6
  • explain without analyze gives the same result. create index on company_id, source_doc doesn't change the plan. Changing the company ID to be unique and re-analyzing the table does make it use the index but obviously I can't do that with the real data - and I still don't understand why it's doing what it's doing. – Richard Wheeldon Sep 6 '20 at 14:57
  • @RichardWheeldon please add explain without analyze to the question description. What do you mean by "changing the company ID to be unique"? – NikitaSerbskiy Sep 6 '20 at 15:09
  • Updated question with answers to both these points. – Richard Wheeldon Sep 7 '20 at 7:36
  • @RichardWheeldon the first and the second queries are the same but I'm sure it's just wrong copy+paste :) As you can see in query plans, in the first case server estimates to return 7 rows which is correct for company_id = 'matalan'. In the second case it expects to find 7839 of rows (which is an average number for all company_ids). This is exactly what I supposed above. – NikitaSerbskiy Sep 7 '20 at 7:52
  • It wasn't a copy-n-paste but it would make sense that that was a timing issue in updating the stats. Is there a threshold below or above which the average rows would cause it to do a full scan or not? How would I figure out that limit? – Richard Wheeldon Sep 7 '20 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.