0

I have two tables that are joined using a join table. I tried to found the best index(es).

DROP TABLE IF EXISTS jobs CASCADE;
DROP TABLE IF EXISTS devices CASCADE;
DROP TABLE IF EXISTS jobs_devices CASCADE;

CREATE TABLE jobs (id INTEGER);
CREATE TABLE devices (id INTEGER);
CREATE TABLE jobs_devices(job_id INTEGER, device_id INTEGER);

INSERT INTO jobs (id) SELECT 
   (random()*20000)::int AS id 
FROM  
   generate_series (1,200000) AS x(n);

INSERT INTO devices (id) SELECT 
   (random()*20000)::int AS id 
FROM  
   generate_series (1,200000) AS x(n);

INSERT INTO jobs_devices (job_id, device_id) SELECT 
   (random()*20000)::int AS job_id,
   (random()*20000)::int AS device_id
FROM  
   generate_series (1,100000) AS x(n);

CREATE INDEX jobs_id_idx ON jobs(id);
CREATE INDEX devices_id_idx ON devices(id);

EXPLAIN ANALYZE SELECT * FROM jobs INNER JOIN jobs_devices ON jobs.id = jobs_devices.job_id WHERE jobs_devices.device_id = 10522;
/*
Merge Join  (cost=1649.51..18003.51 rows=474000 width=12) (actual time=67.770..67.776 rows=8 loops=1)
  Merge Cond: (jobs.id = jobs_devices.job_id)
  ->  Index Only Scan using jobs_id_idx on jobs  (cost=0.42..8744.42 rows=200000 width=4) (actual time=0.095..47.730 rows=84464 loops=1)
        Heap Fetches: 84464
  ->  Sort  (cost=1649.09..1650.28 rows=474 width=8) (actual time=14.654..14.655 rows=1 loops=1)
        Sort Key: jobs_devices.job_id
        Sort Method: quicksort  Memory: 25kB
        ->  Seq Scan on jobs_devices  (cost=0.00..1628.03 rows=474 width=8) (actual time=5.248..14.643 rows=1 loops=1)
              Filter: (device_id = 10522)
              Rows Removed by Filter: 99999
Planning time: 0.303 ms
Execution time: 67.809 ms*/

with multicolumn index

CREATE INDEX jobs_job_id_device_id_idx ON jobs_devices(job_id, device_id);

EXPLAIN ANALYZE SELECT * FROM jobs INNER JOIN jobs_devices ON jobs.id = jobs_devices.job_id WHERE jobs_devices.device_id = 10522;
/*
Nested Loop  (cost=4.50..1900.67 rows=52 width=12) (actual time=3.207..10.676 rows=8 loops=1)
  ->  Seq Scan on jobs_devices  (cost=0.00..1693.00 rows=5 width=8) (actual time=3.175..10.634 rows=1 loops=1)
        Filter: (device_id = 10522)
        Rows Removed by Filter: 99999
  ->  Bitmap Heap Scan on jobs  (cost=4.50..41.43 rows=10 width=4) (actual time=0.020..0.026 rows=8 loops=1)
        Recheck Cond: (id = jobs_devices.job_id)
        Heap Blocks: exact=8
        ->  Bitmap Index Scan on jobs_id_idx  (cost=0.00..4.50 rows=10 width=0) (actual time=0.015..0.015 rows=8 loops=1)
              Index Cond: (id = jobs_devices.job_id)
Planning time: 0.597 ms
Execution time: 10.707 ms
*/

with multicolumn index in right order

DROP INDEX jobs_job_id_device_id_idx;
--multicolumn index
CREATE INDEX jobs_device_id_job_idx ON jobs_devices(device_id, job_id); 

EXPLAIN ANALYZE SELECT * FROM jobs INNER JOIN jobs_devices ON jobs.id = jobs_devices.job_id WHERE jobs_devices.device_id = 10522;
/*
Nested Loop  (cost=4.79..232.05 rows=52 width=12) (actual time=0.044..0.051 rows=8 loops=1)
  ->  Index Only Scan using jobs_device_id_job_idx on jobs_devices  (cost=0.29..24.38 rows=5 width=8) (actual time=0.032..0.032 rows=1 loops=1)
        Index Cond: (device_id = 10522)
        Heap Fetches: 1
  ->  Bitmap Heap Scan on jobs  (cost=4.50..41.43 rows=10 width=4) (actual time=0.010..0.015 rows=8 loops=1)
        Recheck Cond: (id = jobs_devices.job_id)
        Heap Blocks: exact=8
        ->  Bitmap Index Scan on jobs_id_idx  (cost=0.00..4.50 rows=10 width=0) (actual time=0.006..0.006 rows=8 loops=1)
              Index Cond: (id = jobs_devices.job_id)
Planning time: 0.444 ms
Execution time: 0.072 ms
*/

with 2 index and without the multicolumn index

DROP INDEX jobs_device_id_job_idx;
CREATE INDEX jobs_device_id_idx ON jobs_devices(device_id);
CREATE INDEX jobs_job_id_idx ON jobs_devices(job_id);
EXPLAIN ANALYZE SELECT * FROM jobs INNER JOIN jobs_devices ON jobs.id = jobs_devices.job_id WHERE jobs_devices.device_id = 10522;
/*
Nested Loop  (cost=8.83..230.47 rows=52 width=12) (actual time=0.028..0.036 rows=8 loops=1)
  ->  Bitmap Heap Scan on jobs_devices  (cost=4.33..22.80 rows=5 width=8) (actual time=0.017..0.017 rows=1 loops=1)
        Recheck Cond: (device_id = 10522)
        Heap Blocks: exact=1
        ->  Bitmap Index Scan on jobs_device_id_idx  (cost=0.00..4.33 rows=5 width=0) (actual time=0.014..0.014 rows=1 loops=1)
              Index Cond: (device_id = 10522)
  ->  Bitmap Heap Scan on jobs  (cost=4.50..41.43 rows=10 width=4) (actual time=0.009..0.016 rows=8 loops=1)
        Recheck Cond: (id = jobs_devices.job_id)
        Heap Blocks: exact=8
        ->  Bitmap Index Scan on jobs_id_idx  (cost=0.00..4.50 rows=10 width=0) (actual time=0.007..0.007 rows=8 loops=1)
              Index Cond: (id = jobs_devices.job_id)
Planning time: 0.420 ms
Execution time: 0.059 ms
*/

ok.... two indexes...

Now with devices. I want to see if I can use indexes in different situations

with 2 index and without the multicolumn index

EXPLAIN ANALYZE SELECT * FROM devices INNER JOIN jobs_devices ON devices.id = jobs_devices.device_id WHERE jobs_devices.device_id = 10522;
/*
Nested Loop  (cost=8.83..64.87 rows=50 width=12) (actual time=0.032..0.051 rows=10 loops=1)
  ->  Bitmap Heap Scan on devices  (cost=4.50..41.43 rows=10 width=4) (actual time=0.021..0.033 rows=10 loops=1)
        Recheck Cond: (id = 10522)
        Heap Blocks: exact=10
        ->  Bitmap Index Scan on devices_id_idx  (cost=0.00..4.50 rows=10 width=0) (actual time=0.015..0.015 rows=10 loops=1)
              Index Cond: (id = 10522)
  ->  Materialize  (cost=4.33..22.83 rows=5 width=8) (actual time=0.001..0.001 rows=1 loops=10)
        ->  Bitmap Heap Scan on jobs_devices  (cost=4.33..22.80 rows=5 width=8) (actual time=0.007..0.008 rows=1 loops=1)
              Recheck Cond: (device_id = 10522)
              Heap Blocks: exact=1
              ->  Bitmap Index Scan on jobs_device_id_idx  (cost=0.00..4.33 rows=5 width=0) (actual time=0.005..0.005 rows=1 loops=1)
                    Index Cond: (device_id = 10522)
Planning time: 0.130 ms
Execution time: 0.088 ms
*/

but using the initial multicolumn index for our first column

DROP index jobs_device_id_idx;
DROP INDEX jobs_job_id_idx;
CREATE INDEX jobs_job_id_device_id_idx ON jobs_devices(job_id, device_id);
EXPLAIN ANALYZE SELECT * FROM devices INNER JOIN jobs_devices ON devices.id = jobs_devices.device_id WHERE jobs_devices.device_id = 10522;
/*
Nested Loop  (cost=4.50..1735.07 rows=50 width=12) (actual time=4.575..14.733 rows=10 loops=1)
  ->  Bitmap Heap Scan on devices  (cost=4.50..41.43 rows=10 width=4) (actual time=0.013..0.044 rows=10 loops=1)
        Recheck Cond: (id = 10522)
        Heap Blocks: exact=10
        ->  Bitmap Index Scan on devices_id_idx  (cost=0.00..4.50 rows=10 width=0) (actual time=0.010..0.010 rows=10 loops=1)
              Index Cond: (id = 10522)
  ->  Materialize  (cost=0.00..1693.03 rows=5 width=8) (actual time=0.456..1.468 rows=1 loops=10)
        ->  Seq Scan on jobs_devices  (cost=0.00..1693.00 rows=5 width=8) (actual time=4.558..14.670 rows=1 loops=1)
              Filter: (device_id = 10522)
              Rows Removed by Filter: 99999
Planning time: 0.255 ms
Execution time: 14.775 ms
*/

  • What are the pro-cons between 2 indexes (2 Bitmap Heap Scan) on one column and the multicolumn (Index Only Scan)?
  • It seems that 2 indexes are more "multipurpose"?
  • 1
    Did you run a test with two 2-column indexes (job_id, device_id) and (device_id, job_id)? And why aren't the indexes on jobs and devices declared as UNIQUE (or primary keys)? – ypercubeᵀᴹ Mar 3 '18 at 13:19
  • No idea for the unique on primary key. Didn't think about it. And yes I've run the test with two columns indexes. I have good result for the two queries – Mio Mar 3 '18 at 15:03
  • 1
    OK but the question only shows separate tests. I meant to run the test with both 2-clumn indexes existing. So you know which index will be preferred. However, that won't give much info here. You already know which of the two indexes is good for the specific query. For an actual database / application though, you'll need to test many more queries and see which indexes are used and what are the effects on efficiency. – ypercubeᵀᴹ Mar 3 '18 at 15:14
1

Two multcolumn indexes can give you some profit. When you have the id of one of the relations, postgresql would not have to consult the relation table to obtain the id of the other, since it is part of the index. But this will only be noticeable for larger selections. With two indexes on the individual ids, postgresql has to skip between index and table to continue to the other relation.

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.