3

I have three tables, call them table1, table2 and table3. table1 has a foreign key column referencing table2, and table2 has a column referencing table3. Both foreign key columns have a B-tree index over just that column; in addition, table1 has an additional composite index over two columns: (table2_fk, another_column). The tables also have numerous other indexes on their other columns (table1 has five other indexes, each over a single column).

table1 has 67 million entries, table2 has 7400.

The problematic query is

(a) SELECT * 
    FROM table1 
    WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3);

This for some reason runs for several minutes. The two component queries,

(b) SELECT id FROM table2 WHERE table3_fk = 3;
(c) SELECT * FROM table1 WHERE table2_fk IN (13, 14);

both complete instantly. The query (b) returns two IDs (13 and 14). The queries (a) and (c) return 16 rows.

Results of query explainer (with table/column/index names replaced with the above-mentioned aliases) look as follows (edit: added EXPLAIN (ANALYZE, BUFFERS))

db=# explain (ANALYZE, BUFFERS) select * FROM table1 WHERE table2_fk IN (13, 14);
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table1  (cost=1869.86..313991.96 rows=98673 width=193) (actual time=0.033..10.826 rows=18 loops=1)
   Recheck Cond: (table2_fk = ANY ('{13,14}'::integer[]))
   Heap Blocks: exact=6
   Buffers: shared hit=9 read=5
   I/O Timings: read=10.614
   ->  Bitmap Index Scan on table1_table2_fk_idx  (cost=0.00..1845.19 rows=98673 width=0) (actual time=0.016..0.016 rows=18 loops=1)
         Index Cond: (table2_fk = ANY ('{13,14}'::integer[]))
         Buffers: shared hit=8
 Planning time: 49.277 ms
 Execution time: 10.868 ms
(10 rows)


db=# explain (ANALYZE, BUFFERS) select * FROM table1 WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3);
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=15.16..2417375.77 rows=2153302 width=193) (actual time=39974.333..127352.240 rows=18 loops=1)
   Hash Cond: (table1.table2_fk = table2.id)
   Buffers: shared hit=74 read=1536993
   I/O Timings: read=99114.790
   ->  Seq Scan on table1  (cost=0.00..2215354.00 rows=67829000 width=193) (actual time=0.005..119121.005 rows=67398453 loops=1)
         Buffers: shared hit=71 read=1536993
         I/O Timings: read=99114.790
   ->  Hash  (cost=15.11..15.11 rows=4 width=4) (actual time=0.009..0.009 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         Buffers: shared hit=3
         ->  Index Scan using table2_table3_fk_idx on table2  (cost=0.28..15.11 rows=4 width=4) (actual time=0.006..0.007 rows=2 loops=1)
               Index Cond: (table3_fk = 3)
               Buffers: shared hit=3
 Planning time: 0.441 ms
 Execution time: 127352.285 ms
(15 rows)

I.e query (c) uses the index on table2_fk, but query (a) for some reason does not, performing an inefficient sequential scan instead. What could be causing this?

The same database structure exists on many computers (some of which have even more entries in table1), but query (a) is slow only on one machine.

I have tried re-indexing and vacuum/analyze with no help. The behavior didn't change when selecting a single column instead of "select *", or when re-writing (a) using JOIN.

  • 1
    Could you please include the output of EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN? It would show some data about how many rows have been actually found here and there, most possibly giving an explanation. – dezso Dec 14 '15 at 11:36
  • Edited the question, added output of EXPLAIN (ANALYZE, BUFFERS). – laurt Dec 14 '15 at 15:54
  • 1
    And you say the second plan stays when using a JOIN? In this case, this could help: tech.zalando.com/blog/… – dezso Dec 14 '15 at 16:57
2

Try adding an "OFFSET 0" in the subquery like this:

SELECT * 
FROM table1 
WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3 OFFSET 0);
  • Are you sure this is valid in PostgreSQL? – dezso Dec 14 '15 at 13:35
  • The right word is OFFSET, sorry. – Pivobispo Dec 14 '15 at 13:41
  • Ah. But what difference will it make? – dezso Dec 14 '15 at 13:43
  • It will tell Postges to evaluate the subquery first. I had the problem as you some time ago and adding that solved my problem. – Pivobispo Dec 14 '15 at 13:44
  • You can also try WITH t2 AS (SELECT id FROM table2 WHERE table3_fk = 3) SELECT * FROM table1 WHERE table2_fk IN (TABLE t2) ; – ypercubeᵀᴹ Dec 14 '15 at 13:57

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.