CODEWITHSUNDEEP
sqlpostgresql
testing123
testing123

Reputation: 11447

Why doesn't the index get used when the where clause only includes the first column of an index?

Here is the select statement:

SELECT COUNT(*) FROM object_detection_label where company_id = 'SOME_COMPANY_ID'

Number of rows: 47 million

Table columns: id, company_id, job_id, flight_plan_id, media_id, top, left, bottom, right, confidence class_id, classes_version, display_id.

Primary key: id

Index on: company_id, job_id, flight_plan_id, media_id

Results from: EXPLAIN ANALYZE SELECT * FROM object_detection_label where company_id = 'SOME_COMPANY_ID';

"Bitmap Heap Scan on object_detection_label  (cost=41048.21..1042966.23 rows=614131 width=153) (actual time=62.563..216.589 rows=592653 loops=1)"
"  Recheck Cond: ((company_id)::text = 'SOME_COMPANY_ID'::text)"
"  Heap Blocks: exact=14303"
"  ->  Bitmap Index Scan on company_id_job_id_fp_id_media_id_idx  (cost=0.00..40894.67 rows=614131 width=0) (actual time=60.170..60.170 rows=592653 loops=1)"
"        Index Cond: ((company_id)::text = 'SOME_COMPANY_ID'::text)"
"Planning time: 0.061 ms"
"Execution time: 316.966 ms"

Results from: EXPLAIN ANALYZE SELECT * FROM object_detection_label where company_id = 'SOME_COMPANY_ID' and job_id = 'SOME_JOB_ID';

"Index Scan using company_id_job_id_fp_id_media_id_idx on object_detection_label  (cost=0.69..418.71 rows=102 width=153) (actual time=0.064..6.912 rows=13206 loops=1)"
"  Index Cond: (((company_id)::text = 'CHURCH_OF_JESUS_CHRIST'::text) AND ((job_id)::text = '5cc085baa635404e54ebd46e'::text))"
"Planning time: 0.110 ms"
"Execution time: 10.114 ms"

Notice it is using a Bitmap Heat Scan rather than an Index Scan (which it uses if I include the job_id) as part of the where clause.

After adding an index to just the company_id it still didn't use an index scan. Why is this the case? How can I get it to use the index scan?

Upvotes: 1

Views: 49

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246133

A Bitmap Index Scan is a kind of Index Scan.

The result set is 600000 rows out of your 47000000, and they are found in 14000 blocks.

That means that with a normal index scan each block would have to be visited several times, which is inefficient. A Bitmap Index Scan fetches the required table blocks in sequential order, and each block is fetched only once. PostgreSQL estimates this to be more efficient, and it is probably right.

You can verify this by re-running your query after setting

SET enable_bitmapscan = off;

Upvotes: 2

Related Questions