2

I'm hoping someone here can help me understand why Postgres' query planner is opting for a 200-minute-long sequential scan, rather than using a lightning-fast index.

We have seven sets of (FooClaims, FooClaimLines) tables, e.g. (CarrierClaims, CarrierClaimLines). For the five smaller sets of tables, the query planner is doing the correct thing and utilizing the indexes on both tables when we query them. But for the two largest sets of tables, it opts to use a table scan, instead.

The query I'm having trouble with looks like this (explain output is included further below):

select * from "FooClaims" inner join "FooClaimLines" on "FooClaims"."claimId" = "FooClaimLines"."parentClaim" where "FooClaims"."beneficiaryId" = '12345';

Roughly speaking, the tables look like this (full details included further below):

                          Table "public.FooClaims"
                 Column                  |         Type          | Modifiers 
-----------------------------------------+-----------------------+-----------
 claimId                                 | character varying(15) | not null
 beneficiaryId                           | character varying(15) | not null
... (other not relevant columns)
Indexes:
    "FooClaims_pkey" PRIMARY KEY, btree ("claimId")
    "FooClaims_beneficiaryId_idx" btree ("beneficiaryId")
Foreign-key constraints:
    "FooClaims_beneficiaryId_to_Beneficiaries" FOREIGN KEY ("beneficiaryId") REFERENCES "Beneficiaries"("beneficiaryId")
Referenced by:
    TABLE ""FooClaimLines"" CONSTRAINT "FooClaimLines_parentClaim_to_FooClaims" FOREIGN KEY ("parentClaim") REFERENCES "FooClaims"("claimId")
Tablespace: "fooclaims_ts"

                      Table "public.FooClaimLines"
               Column                |         Type          | Modifiers 
-------------------------------------+-----------------------+-----------
 lineNumber                          | numeric               | not null
 parentClaim                         | character varying(15) | not null
... (other not relevant columns)
Indexes:
    "FooClaimLines_pkey" PRIMARY KEY, btree ("parentClaim", "lineNumber")
Foreign-key constraints:
    "FooClaimLines_parentClaim_to_FooClaims" FOREIGN KEY ("parentClaim") REFERENCES "FooClaims"("claimId")
Tablespace: "fooclaimlines_ts"

These are big tables:

  • CarrierClaims: 3 billion rows
  • CarrierClaimLines: 7 billion rows
  • OutpatientClaims: 600 million rows
  • OutpatientClaimLines: 4 billion rows
  • DMEClaims: 230 million rows
  • DMEClaimLines: 420 million rows
  • Beneficiaries: 66 million rows

For the smaller tables, this query uses all of the indexes and runs in milliseconds. For the two larger tables, it goes for a Seq Scan of the FooClaimLines table and takes about 200 minutes. Gah!

And if I break apart the joined query, it uses the indexes just fine even on the largest tables, e.g.:

select "claimId" from "FooClaims" where "FooClaims"."beneficiaryId" = '12345' limit 5;
select * from "FooClaimLines" where "FooClaimLines"."parentClaim" in ('1', '2', '3', '4', '5');

Also -- and this seems really weird to me -- if I switch the joined queries to a select count(*) ... instead of a select * ..., the query planner will decide to use the indexes even on the largest tables.

Bluntly: I'm not super familiar with how PostgreSQL makes its query planning decisions, and I'm unsure what might be causing this behavior.

Things I've Tried

  1. Crying.
  2. Running a vacuum freeze analyze on all of the problem table pairs.
  3. And now: asking you, friendly internet stranger, for help.

My backup plan here is, of course, to rejigger our app to not use the inner join query and instead perform the query manually. I'd really prefer to avoid that if at all possible, though.

Full Details

Full Details: explain Output

Here's the explain output for one of the "slow" queries:

> explain select * from "OutpatientClaims" inner join "OutpatientClaimLines" on "OutpatientClaims"."claimId" = "OutpatientClaimLines"."parentClaim" where "OutpatientClaims"."beneficiaryId" = '12345';                                                      
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=21892.78..135879128.24 rows=39638 width=2110)
   Hash Cond: (("OutpatientClaimLines"."parentClaim")::text = ("OutpatientClaims"."claimId")::text)
   ->  Seq Scan on "OutpatientClaimLines"  (cost=0.00..119688035.24 rows=4311681024 width=233)
   ->  Hash  (cost=21824.66..21824.66 rows=5449 width=1877)
         ->  Bitmap Heap Scan on "OutpatientClaims"  (cost=202.80..21824.66 rows=5449 width=1877)
               Recheck Cond: (("beneficiaryId")::text = '12345'::text)
               ->  Bitmap Index Scan on "OutpatientClaims_beneficiaryId_idx"  (cost=0.00..201.44 rows=5449 width=0)
                     Index Cond: (("beneficiaryId")::text = '12345'::text)
(8 rows)

And for one of the "fast" queries:

> explain select * from "DMEClaims" inner join "DMEClaimLines" on "DMEClaims"."claimId" = "DMEClaimLines"."parentClaim" where "DMEClaims"."beneficiaryId" = '12345';                                                                                                           
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=85.06..3401617.25 rows=3960 width=411)
   ->  Bitmap Heap Scan on "DMEClaims"  (cost=84.49..8227.57 rows=2054 width=218)
         Recheck Cond: (("beneficiaryId")::text = '12345'::text)
         ->  Bitmap Index Scan on "DMEClaims_beneficiaryId_idx"  (cost=0.00..83.97 rows=2054 width=0)
               Index Cond: (("beneficiaryId")::text = '12345'::text)
   ->  Index Scan using "DMEClaimLines_pkey" on "DMEClaimLines"  (cost=0.57..1646.38 rows=571 width=193)
         Index Cond: (("parentClaim")::text = ("DMEClaims"."claimId")::text)
(7 rows)

Full Details: Other

Update #1: enable_seq_scan = false

If I tell Postgres to only run a sequential scan if it really really has to, the query planner starts behaving:

> set enable_seqscan = false;
SET
Time: 0.545 ms

> explain select * from "OutpatientClaims" inner join "OutpatientClaimLines" on "OutpatientClaims"."claimId" = "OutpatientClaimLines"."parentClaim" where "OutpatientClaims"."beneficiaryId" = '12345';
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=203.51..506205086.02 rows=39638 width=2110)
   ->  Bitmap Heap Scan on "OutpatientClaims"  (cost=202.80..21824.66 rows=5449 width=1877)
         Recheck Cond: (("beneficiaryId")::text = '12345'::text)
         ->  Bitmap Index Scan on "OutpatientClaims_beneficiaryId_idx"  (cost=0.00..201.44 rows=5449 width=0)
               Index Cond: (("beneficiaryId")::text = '12345'::text)
   ->  Index Scan using "OutpatientClaimLines_pkey" on "OutpatientClaimLines"  (cost=0.71..92576.04 rows=31867 width=233)
         Index Cond: (("parentClaim")::text = ("OutpatientClaims"."claimId")::text)
(7 rows)

Time: 2.560 ms

That seems like a pretty brute force approach to me, though. Is there a better way to convince the query planner that this is the sane thing to do?

Improve this question
3
  • SELECT * will need every column in the table to be returned, for whatever rows match. So, a table scan makes sense - otherwise, it has to look up every row it finds to get the additional required columns. If you don't need all the columns, don't do a SELECT *; you might get a much faster query. SELECT COUNT(*) needs a count of rows only, no specific row data; if an index covers the WHERE clause, then the index has all the information necessary to return a result.
    – RDFozz
    Commented Mar 26, 2018 at 22:30
  • @RDFozz: Except that a table scan doesn't make sense: it should be using the index to identify where the rows it's going to pull are, so that it doesn't have to churn through all of the other, non-matching rows (over the course of 200 minutes) just to find the matching ones. The much more sensible behavior it's exhibiting on the other tables is dramatically faster. Why the difference?
    – Karl M. Davis
    Commented Mar 27, 2018 at 2:26
  • I'm not an expert in PostgreSQL, so I can't say for certain. In SQL Server, statistics on the table in question would lead the engine to decide that it would be faster to check the table directly, than to use the index, and then to grab the necessary pages out of the table to get the rows. If most of the pages of the table would be pulled in in the process of getting the rows, then it might be faster to just scan the table that to hit the index, look up each row, and grab the pages out of the table. Disk reads are the most expensive operation in most searches.
    – RDFozz
    Commented Mar 27, 2018 at 14:29

1 Answer 1

Reset to default
-2

you don't even shows us the definition of "OutpatientClaimLines"."parentClaim" how could we know why it's not using an index on that table?

Moreover, you have a lot of mistakes in this schema.

  1. Stop using capital letters and double quotes anywhere.
  2. No join condition should be on a varchar/text (non-int) field if you've got billions of rows. Use bigint or UUID.
  3. Why are you calling it parentClaim. That's just a foreign key. you don't call all the foreign key's that reference other tables parent. That's a really bad convention. You call something a parent if there is a parent/child relationship.

I think you need a consultant.

Improve this answer
7
  • 1
    Do you have any links or references that provide more information on how your suggestion #2 might help in this situation?
    – Karl M. Davis
    Commented Mar 27, 2018 at 2:23
  • And the definition for OutpatientClaimLines.parentClaim is in one of the reference links that I provided: "character varying(15)"
    – Karl M. Davis
    Commented Mar 27, 2018 at 2:27
  • 1
    @KarlM.Davis it's a function of math. A varchar field has overhead pertaining to the length of 4bytes. So the size is 19 bytes to store 15 bytes. Also, you have less entropy in a character than you do in an int .. In the varchar(1) for instance, you can't store \0, and you likely can't/won't store control chars (at the very least).
    – Evan Carroll
    Commented Mar 27, 2018 at 3:11
  • So for instance, if you're storing base62/64 in a char (as is normally done), you're losing 256**12 - 62**15 bits of entropy. Further you're paying the price over of 4 bytes. which itself would give you substantially more entropy (256**2 per byte) than your current scheme.
    – Evan Carroll
    Commented Mar 27, 2018 at 3:20
  • 1
    Can items 1 and 3 actually impact the speed of a search?
    – RDFozz
    Commented Mar 27, 2018 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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