PostgreSQL query optimization - can I force the use of an index on a lookup table?

user_11058068 0
Viewed 86

I am attempting to optimize indexes and querying for a postgres database, and have run into some issues around a lookup table I have used to support joins for a many:many relationship. Here is the schema of the database (please note I have pared down these tables somewhat significantly to aid in creating a more effective example):

-- variant_fact table
CREATE TABLE variant_fact(
    variant_id bigserial PRIMARY KEY,
    variant_name NOT NULL,
    variant_attributes jsonb
);
CREATE INDEX variant_name_lookup ON public.variant_fact (variant_name ASC);

-- the faulty ensembl_lookup table
CREATE TABLE ensembl_lookup(
    lookup_id bigserial PRIMARY KEY,
    variant_id bigint NOT NULL,
    feature_id bigint NOT NULL
);
CREATE INDEX ensembl_lookup_feature_id_index ON public.ensembl_lookup (feature_id ASC);
CREATE INDEX ensembl_lookup_variant_id_index ON public.ensembl_lookup (variant_id ASC);

-- feature_fact table
CREATE TABLE feature_fact(
    feature_id bigserial PRIMARY KEY,
    feature_type VARCHAR(100),
    enst_id integer
);
CREATE INDEX fki_feature_table_fk_ensembl_transcript ON feature_fact (enst_id ASC);

-- ensembl_transcript table
CREATE TABLE ensembl_transcript(
    enst_id bigserial PRIMARY KEY,
    enst_accession VARCHAR(100) NOT NULL
);
CREATE INDEX ensembl_transcript_lookup ON ensembl_transcript (enst_accession ASC);

If I perform a lookup query on the variant_fact table and join across the lookup table to the rest of the db, the query is fast and no Seqscans are performed:

EXPLAIN ANALYZE SELECT * FROM variant_fact var 
INNER JOIN ensembl_lookup lookup ON var.variant_id = lookup.variant_id 
INNER JOIN feature_fact feat ON lookup.feature_id = feat.feature_id 
INNER JOIN ensembl_transcript tran ON feat.enst_id = tran.enst_id 
WHERE var.variant_name = 'rs1161941853' LIMIT 10000;

                                                                                        QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.29..15645.70 rows=100 width=428) (actual time=5.433..8.909 rows=18 loops=1)
   ->  Nested Loop  (cost=2.29..374036.36 rows=2391 width=428) (actual time=5.432..8.906 rows=18 loops=1)
         ->  Nested Loop  (cost=1.86..353999.35 rows=2391 width=388) (actual time=4.557..7.994 rows=19 loops=1)
               ->  Nested Loop  (cost=1.28..333558.57 rows=2391 width=340) (actual time=3.171..4.989 rows=19 loops=1)
                     ->  Index Scan using variant_name_lookup on variant_fact var  (cost=0.70..2197.85 rows=544 width=316) (actual time=1.541..1.890 rows=2 loops=1)
                           Index Cond: (variant_name = 'rs1161941853'::text)
                     ->  Index Scan using fki_ensembl_lookup_fk_variant_fact on ensembl_lookup lookup  (cost=0.58..505.58 rows=10354 width=24) (actual time=1.320..1.545 rows=10 loops=2)
                           Index Cond: (variant_id = var.variant_id)
               ->  Index Scan using feature_table_pkey on feature_fact feat  (cost=0.58..8.55 rows=1 width=48) (actual time=0.158..0.158 rows=1 loops=19)
                     Index Cond: (feature_id = lookup.feature_id)
         ->  Index Scan using ensembl_transcript_pkey on ensembl_transcript tran  (cost=0.42..8.38 rows=1 width=40) (actual time=0.047..0.047 rows=1 loops=19)
               Index Cond: (enst_id = feat.enst_id)
 Planning Time: 19.609 ms
 Execution Time: 9.055 ms
(14 rows)

However, I also need to support the use case of looking up date by querying ensembl_transcript and joining across the lookup table to variant_fact. When I perform this query, I see much worse performance and I see that a SeqScan is being performed on the lookup table despite both columns being indexed:

EXPLAIN ANALYZE SELECT * FROM ensembl_transcript tran
INNER JOIN feature_fact feat ON tran.enst_id = feat.enst_id
INNER JOIN ensembl_lookup lookup ON feat.feature_id = lookup.feature_id
INNER JOIN variant_fact var ON lookup.variant_id = var.variant_id
WHERE tran.enst_accession = 'ENST00000261302' LIMIT 10000;
                                                                                                  QUERY PLAN                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=186460.42..30770634.25 rows=10000 width=428) (actual time=278616.865..278677.569 rows=10000 loops=1)
   ->  Gather  (cost=186460.42..54048248.96 rows=17611 width=428) (actual time=278364.349..278424.255 rows=10000 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Nested Loop  (cost=185460.42..54045487.86 rows=7338 width=428) (actual time=278327.722..278364.631 rows=3335 loops=3)
               ->  Hash Join  (cost=185459.84..53982826.22 rows=7338 width=112) (actual time=278292.333..278303.233 rows=3335 loops=3)
                     Hash Cond: (lookup.feature_id = feat.feature_id)
                     ->  Parallel Seq Scan on ensembl_lookup lookup  (cost=0.00..46849521.00 rows=1852739200 width=24) (actual time=0.425..184913.287 rows=468118071 loops=3)
                     ->  Hash  (cost=185190.54..185190.54 rows=21544 width=88) (actual time=409.762..409.764 rows=95847 loops=3)
                           Buckets: 131072 (originally 32768)  Batches: 1 (originally 1)  Memory Usage: 11508kB
                           ->  Nested Loop  (cost=1.00..185190.54 rows=21544 width=88) (actual time=285.080..372.500 rows=95847 loops=3)
                                 ->  Index Scan using enst_accession_unique on ensembl_transcript tran  (cost=0.42..8.44 rows=1 width=40) (actual time=0.067..0.068 rows=1 loops=3)
                                       Index Cond: ((enst_accession)::text = 'ENST00000261302'::text)
                                 ->  Index Scan using fki_feature_table_fk_ensembl_transcript on feature_fact feat  (cost=0.58..184266.29 rows=91582 width=48) (actual time=0.083..74.785 rows=95847 loops=3)
                                       Index Cond: (enst_id = tran.enst_id)
               ->  Index Scan using variant_fact_pkey on variant_fact var  (cost=0.57..8.54 rows=1 width=316) (actual time=0.018..0.018 rows=1 loops=10004)
                     Index Cond: (variant_id = lookup.variant_id)
 Planning Time: 0.765 ms
 JIT:
   Functions: 67
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 10.777 ms, Inlining 161.967 ms, Optimization 595.517 ms, Emission 348.565 ms, Total 1116.827 ms
 Execution Time: 278681.483 ms
(23 rows)

My question is How Can I optimize my indexes or queries such that I can join on my lookup table efficiently in either direction?

I have thought about going in a few directions, including

  1. Clustering the table ensembl_lookup on feature_id to make lookups more efficient
  2. Tweaking postgres parameters to discourage SeqScans

Neither of these have worked out too well for me, is there anything else I should consider? Thanks

indexing postgresql sql
1 Answers

I'd expect that using the index on ensembl_lookup(feature_id) for a nested loop join would give a benefit, but PostgreSQL thinks otherwise.

As a first, measure, I's ANALYZE all the tables involved. Perhaps up-to-date statistics will do the trick.

Next, you can check if PostgreSQL is right or not. Change enable_hashjoin and enable_mergejoin to off on the command line to force PostgreSLQ to use a nested loop join. Does query execution become faster or not?

If that gets you a better plan, perhaps random_page_cost is too high for your hardware (the initial setting suggests spinning disks) or effective_cache_size is to low for your hardware (the initial setting suggests that there are 4GB available to cache data). Changing these parameters can promote the use of an index scan.

Another thing you could tune is to disable parallel query by setting max_parallel_workers to 0. Then the sequential scan will be single-threaded and take three times as long, which will make the nested loop plan more attractive. You probably don't want to disable parallel query for your whole workload, however.

user_64643080
Related