Skip to content

Instantly share code, notes, and snippets.

@okabe-yuya

okabe-yuya/verification_multiindex.md

Last active March 9, 2023 01:43
Star
Embed
What would you like to do?
Learn more about clone URLs
Download ZIP
中間テーブルに対して作成したインデックスの種類による実行結果
Raw
verification_multiindex.md

検証結果

中間テーブルの情報。現在はインデックスはuser_product_idにのみ付与。

                                       Table "public.user_products"
     Column      |  Type   | Collation | Nullable |                        Default                         
-----------------+---------+-----------+----------+--------------------------------------------------------
 user_product_id | integer |           | not null | nextval('user_products_user_product_id_seq'::regclass)
 user_id         | integer |           |          | 
 product_id      | integer |           |          | 
Indexes:
    "user_products_pkey" PRIMARY KEY, btree (user_product_id)
Foreign-key constraints:
    "user_products_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(product_id)
    "user_products_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id)

初期データの投入。1v1v1のシンプルなデータ。

BEGIN;
  INSERT INTO users(user_name) SELECT format('ユーザーNo.%s', i) FROM generate_series(1, 10000) as i;
  INSERT INTO products(product_name) SELECT format('プロダクトNo.%s', i) FROM generate_series(1, 10000) as i;
  INSERT INTO user_products(user_id, product_id) SELECT i, i FROM generate_series(1, 10000) as i;
COMMIT;

postgres=# SELECT COUNT(*) FROM users;
 count 
-------
 10000
(1 row)

postgres=# SELECT COUNT(*) FROM products;
 count 
-------
 10000
(1 row)

postgres=# SELECT COUNT(*) FROM user_products;
 count 
-------
 10000
(1 row)

A

EXPLAIN ANALYZE SELECT
  *
FROM
  user_products AS up
JOIN
  users AS u
ON
  up.user_id = u.user_id
JOIN
  products AS p
ON
  up.product_id = p.product_id
;

B

EXPLAIN ANALYZE SELECT
  *
FROM
  user_products AS up
JOIN
  users AS u
ON
  up.user_id = u.user_id
JOIN
  products AS p
ON
  up.product_id = p.product_id
WHERE
  u.user_name = 'ユーザーNo.5000' AND
  p.product_name = 'プロダクトNo.5000'
;

C

EXPLAIN ANALYZE SELECT * FROM user_products WHERE user_id = 5000 AND product_id = 5000;

インデックスなし

A

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=588.00..795.52 rows=10000 width=61) (actual time=5.925..13.584 rows=10000 loops=1)
   Hash Cond: (up.product_id = p.product_id)
   ->  Hash Join  (cost=289.00..470.26 rows=10000 width=35) (actual time=3.049..7.787 rows=10000 loops=1)
         Hash Cond: (up.user_id = u.user_id)
         ->  Seq Scan on user_products up  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.017..1.146 rows=10000 loops=1)
         ->  Hash  (cost=164.00..164.00 rows=10000 width=23) (actual time=2.978..2.980 rows=10000 loops=1)
               Buckets: 16384  Batches: 1  Memory Usage: 674kB
               ->  Seq Scan on users u  (cost=0.00..164.00 rows=10000 width=23) (actual time=0.008..1.119 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=2.849..2.849 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on products p  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.013..1.125 rows=10000 loops=1)
 Planning Time: 0.377 ms
 Execution Time: 14.326 ms
(13 rows)

B

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=189.30..370.68 rows=1 width=61) (actual time=2.141..3.094 rows=1 loops=1)
   ->  Hash Join  (cost=189.01..370.27 rows=1 width=35) (actual time=2.095..3.047 rows=1 loops=1)
         Hash Cond: (up.user_id = users.user_id)
         ->  Seq Scan on user_products up  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.008..0.885 rows=10000 loops=1)
         ->  Hash  (cost=189.00..189.00 rows=1 width=23) (actual time=1.114..1.114 rows=1 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on users  (cost=0.00..189.00 rows=1 width=23) (actual time=0.539..1.110 rows=1 loops=1)
                     Filter: ((user_name)::text = 'ユーザーNo.5000'::text)
                     Rows Removed by Filter: 9999
   ->  Index Scan using products_pkey on products  (cost=0.29..0.35 rows=1 width=26) (actual time=0.035..0.035 rows=1 loops=1)
         Index Cond: (product_id = up.product_id)
         Filter: ((product_name)::text = 'プロダクトNo.5000'::text)
 Planning Time: 0.327 ms
 Execution Time: 3.136 ms
(14 rows)

C

                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Seq Scan on user_products  (cost=0.00..205.00 rows=1 width=12) (actual time=0.928..1.813 rows=1 loops=1)
   Filter: ((user_id = 5000) AND (product_id = 5000))
   Rows Removed by Filter: 9999
 Planning Time: 0.122 ms
 Execution Time: 1.840 ms
(5 rows)

個別にインデックス

CREATE INDEX user_products_user_id_idx ON user_products (user_id);
CREATE INDEX user_products_product_id_idx ON user_products (product_id);

A

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=588.00..795.52 rows=10000 width=61) (actual time=5.514..13.297 rows=10000 loops=1)
   Hash Cond: (up.product_id = p.product_id)
   ->  Hash Join  (cost=289.00..470.26 rows=10000 width=35) (actual time=2.636..7.350 rows=10000 loops=1)
         Hash Cond: (up.user_id = u.user_id)
         ->  Seq Scan on user_products up  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.013..1.087 rows=10000 loops=1)
         ->  Hash  (cost=164.00..164.00 rows=10000 width=23) (actual time=2.543..2.546 rows=10000 loops=1)
               Buckets: 16384  Batches: 1  Memory Usage: 674kB
               ->  Seq Scan on users u  (cost=0.00..164.00 rows=10000 width=23) (actual time=0.006..0.868 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=2.862..2.863 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on products p  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.009..1.060 rows=10000 loops=1)
 Planning Time: 0.371 ms
 Execution Time: 14.117 ms
(13 rows)

B

                                                                       QUERY PLAN                                                        
                
-----------------------------------------------------------------------------------------------------------------------------------------
----------------
 Nested Loop  (cost=0.57..197.72 rows=1 width=61) (actual time=0.779..1.328 rows=1 loops=1)
   ->  Nested Loop  (cost=0.29..197.31 rows=1 width=35) (actual time=0.767..1.315 rows=1 loops=1)
         ->  Seq Scan on users  (cost=0.00..189.00 rows=1 width=23) (actual time=0.746..1.292 rows=1 loops=1)
               Filter: ((user_name)::text = 'ユーザーNo.5000'::text)
               Rows Removed by Filter: 9999
         ->  Index Scan using user_products_user_id_idx on user_products up  (cost=0.29..8.30 rows=1 width=12) (actual time=0.016..0.018 
rows=1 loops=1)
               Index Cond: (user_id = users.user_id)
   ->  Index Scan using products_pkey on products  (cost=0.29..0.35 rows=1 width=26) (actual time=0.010..0.010 rows=1 loops=1)
         Index Cond: (product_id = up.product_id)
         Filter: ((product_name)::text = 'プロダクトNo.5000'::text)
 Planning Time: 0.435 ms
 Execution Time: 1.407 ms
(12 rows)

C

                                                                 QUERY PLAN                                                              
    
-----------------------------------------------------------------------------------------------------------------------------------------
----
 Index Scan using user_products_product_id_idx on user_products  (cost=0.29..8.30 rows=1 width=12) (actual time=4.621..4.626 rows=1 loops
=1)
   Index Cond: (product_id = 5000)
   Filter: (user_id = 5000)
 Planning Time: 0.131 ms
 Execution Time: 4.653 ms
(5 rows)

複合インデックス

A

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=588.00..795.52 rows=10000 width=61) (actual time=3.446..6.453 rows=10000 loops=1)
   Hash Cond: (up.product_id = p.product_id)
   ->  Hash Join  (cost=289.00..470.26 rows=10000 width=35) (actual time=1.452..3.239 rows=10000 loops=1)
         Hash Cond: (up.user_id = u.user_id)
         ->  Seq Scan on user_products up  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.007..0.480 rows=10000 loops=1)
         ->  Hash  (cost=164.00..164.00 rows=10000 width=23) (actual time=1.408..1.408 rows=10000 loops=1)
               Buckets: 16384  Batches: 1  Memory Usage: 674kB
               ->  Seq Scan on users u  (cost=0.00..164.00 rows=10000 width=23) (actual time=0.006..0.560 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=1.939..1.939 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on products p  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.012..0.732 rows=10000 loops=1)
 Planning Time: 11.842 ms
 Execution Time: 6.873 ms
(13 rows)

B

                                                                        QUERY PLAN                                                       
                 
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------
 Nested Loop  (cost=0.57..197.72 rows=1 width=61) (actual time=4.030..4.708 rows=1 loops=1)
   ->  Nested Loop  (cost=0.29..197.31 rows=1 width=35) (actual time=3.953..4.629 rows=1 loops=1)
         ->  Seq Scan on users  (cost=0.00..189.00 rows=1 width=23) (actual time=0.638..1.310 rows=1 loops=1)
               Filter: ((user_name)::text = 'ユーザーNo.5000'::text)
               Rows Removed by Filter: 9999
         ->  Index Scan using user_products_multiple_idx on user_products up  (cost=0.29..8.30 rows=1 width=12) (actual time=3.304..3.306
 rows=1 loops=1)
               Index Cond: (user_id = users.user_id)
   ->  Index Scan using products_pkey on products  (cost=0.29..0.35 rows=1 width=26) (actual time=0.068..0.068 rows=1 loops=1)
         Index Cond: (product_id = up.product_id)
         Filter: ((product_name)::text = 'プロダクトNo.5000'::text)
 Planning Time: 0.365 ms
 Execution Time: 4.750 ms
(12 rows)

C

                                                                QUERY PLAN                                                               
  
-----------------------------------------------------------------------------------------------------------------------------------------
--
 Index Scan using user_products_multiple_idx on user_products  (cost=0.29..8.30 rows=1 width=12) (actual time=0.032..0.034 rows=1 loops=1
)
   Index Cond: ((user_id = 5000) AND (product_id = 5000))
 Planning Time: 0.451 ms
 Execution Time: 0.060 ms
(4 rows)

合わせ技

A

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=588.00..795.52 rows=10000 width=61) (actual time=5.559..11.752 rows=10000 loops=1)
   Hash Cond: (up.product_id = p.product_id)
   ->  Hash Join  (cost=289.00..470.26 rows=10000 width=35) (actual time=2.420..6.217 rows=10000 loops=1)
         Hash Cond: (up.user_id = u.user_id)
         ->  Seq Scan on user_products up  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.006..0.822 rows=10000 loops=1)
         ->  Hash  (cost=164.00..164.00 rows=10000 width=23) (actual time=2.369..2.371 rows=10000 loops=1)
               Buckets: 16384  Batches: 1  Memory Usage: 674kB
               ->  Seq Scan on users u  (cost=0.00..164.00 rows=10000 width=23) (actual time=0.004..0.884 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=3.113..3.114 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on products p  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.021..1.393 rows=10000 loops=1)
 Planning Time: 30.952 ms
 Execution Time: 12.599 ms
(13 rows)

B

                                                                       QUERY PLAN                                                        
                
-----------------------------------------------------------------------------------------------------------------------------------------
----------------
 Nested Loop  (cost=0.57..197.72 rows=1 width=61) (actual time=4.711..5.279 rows=1 loops=1)
   ->  Nested Loop  (cost=0.29..197.31 rows=1 width=35) (actual time=4.550..5.117 rows=1 loops=1)
         ->  Seq Scan on users  (cost=0.00..189.00 rows=1 width=23) (actual time=0.646..1.209 rows=1 loops=1)
               Filter: ((user_name)::text = 'ユーザーNo.5000'::text)
               Rows Removed by Filter: 9999
         ->  Index Scan using user_products_user_id_idx on user_products up  (cost=0.29..8.30 rows=1 width=12) (actual time=3.895..3.898 
rows=1 loops=1)
               Index Cond: (user_id = users.user_id)
   ->  Index Scan using products_pkey on products  (cost=0.29..0.35 rows=1 width=26) (actual time=0.153..0.154 rows=1 loops=1)
         Index Cond: (product_id = up.product_id)
         Filter: ((product_name)::text = 'プロダクトNo.5000'::text)
 Planning Time: 0.443 ms
 Execution Time: 5.321 ms
(12 rows)

C

                                                                 QUERY PLAN                                                              
    
-----------------------------------------------------------------------------------------------------------------------------------------
----
 Index Scan using user_products_product_id_idx on user_products  (cost=0.29..8.30 rows=1 width=12) (actual time=4.077..4.082 rows=1 loops
=1)
   Index Cond: (product_id = 5000)
   Filter: (user_id = 5000)
 Planning Time: 0.354 ms
 Execution Time: 4.121 ms
(5 rows)

result

A: JOINのみ B: JOIN+WHERE C: WHEREのみ
インデックスなし 14.326ms 3.136 ms 1.840 ms
単一インデックス 14.117 ms 1.407 ms 4.653 ms
複合インデックス 6.873 ms 4.750 ms 0.060 ms
単一と複合インデックス 12.599 ms 5.321 ms 4.121 ms
A: JOINのみ B: JOIN+WHERE C: WHEREのみ
インデックスなし SeqScan SeqScan SeqScan
単一インデックス SeqScan SeqScanとIndexScan IndexScan
複合インデックス SeqScan SeqScanとIndexScan IndexScan
単一と複合インデックス SeqScan SeqScanとIndexScan IndexScan
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment