Skip to content

Instantly share code, notes, and snippets.

@joshuawscott

joshuawscott/id and name indexes

Last active August 29, 2015 14:08
Show Gist options
  • Save joshuawscott/64bb4bda1acce96540e2 to your computer and use it in GitHub Desktop.
Save joshuawscott/64bb4bda1acce96540e2 to your computer and use it in GitHub Desktop.
Download ZIP
100,000,000 rows in postgres.
Raw
id and name indexes
jscott=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+------------------------+-----------
id | integer |
name | character varying(255) |
Indexes:
"id_idx" btree (id)
"idx_name" btree (name)
jscott=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+------------------------+-----------
id | integer |
name | character varying(255) |
Indexes:
"id_idx2" btree (id)
"id_name2" btree (name)
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id;
QUERY PLAN
---------------------------------------------------------------------------------------
Merge Join (cost=456.64..7857840.34 rows=101000000 width=22)
Merge Cond: (t1.id = t2.id)
-> Index Scan using id_idx on t1 (cost=0.00..3171218.83 rows=101000000 width=11)
-> Index Scan using id_idx2 on t2 (cost=0.00..3172311.76 rows=101000000 width=11)
(4 rows)
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id WHERE t1.id = 23942;
QUERY PLAN
--------------------------------------------------------------------------
Nested Loop (cost=0.00..38.70 rows=1 width=22)
-> Index Scan using id_idx on t1 (cost=0.00..19.34 rows=1 width=11)
Index Cond: (id = 23942)
-> Index Scan using id_idx2 on t2 (cost=0.00..19.34 rows=1 width=11)
Index Cond: (id = 23942)
(5 rows)
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id WHERE t1.name = 'name_45';
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join (cost=605565.37..3920172.70 rows=996533 width=22)
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2 (cost=0.00..1555946.00 rows=101000000 width=11)
-> Hash (cost=588242.71..588242.71 rows=996533 width=11)
-> Bitmap Heap Scan on t1 (cost=18666.46..588242.71 rows=996533 width=11)
Recheck Cond: ((name)::text = 'name_45'::text)
-> Bitmap Index Scan on idx_name (cost=0.00..18417.32 rows=996533 width=0)
Index Cond: ((name)::text = 'name_45'::text)
(8 rows)
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id WHERE t2.name = 'name_45';
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join (cost=606728.20..3921035.53 rows=976333 width=22)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 (cost=0.00..1555946.00 rows=101000000 width=11)
-> Hash (cost=589756.04..589756.04 rows=976333 width=11)
-> Bitmap Heap Scan on t2 (cost=18289.91..589756.04 rows=976333 width=11)
Recheck Cond: ((name)::text = 'name_45'::text)
-> Bitmap Index Scan on id_name2 (cost=0.00..18045.82 rows=976333 width=0)
Index Cond: ((name)::text = 'name_45'::text)
(8 rows)
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id WHERE t1.name = 'name_45' and t2.name = 'name_45';
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join (cost=625394.66..1217041.24 rows=9633 width=22)
Hash Cond: (t1.id = t2.id)
-> Bitmap Heap Scan on t1 (cost=18666.46..588242.71 rows=996533 width=11)
Recheck Cond: ((name)::text = 'name_45'::text)
-> Bitmap Index Scan on idx_name (cost=0.00..18417.32 rows=996533 width=0)
Index Cond: ((name)::text = 'name_45'::text)
-> Hash (cost=589756.04..589756.04 rows=976333 width=11)
-> Bitmap Heap Scan on t2 (cost=18289.91..589756.04 rows=976333 width=11)
Recheck Cond: ((name)::text = 'name_45'::text)
-> Bitmap Index Scan on id_name2 (cost=0.00..18045.82 rows=976333 width=0)
Index Cond: ((name)::text = 'name_45'::text)
(11 rows)
jscott=# explain select * from (select * from t1 where t1.name = 'name_45') ft1 inner join (select * from t2 where t2.name = 'name_45') ft2 on ft1.id = ft2.id;
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join (cost=625394.66..1217041.24 rows=9633 width=22)
Hash Cond: (t1.id = t2.id)
-> Bitmap Heap Scan on t1 (cost=18666.46..588242.71 rows=996533 width=11)
Recheck Cond: ((name)::text = 'name_45'::text)
-> Bitmap Index Scan on idx_name (cost=0.00..18417.32 rows=996533 width=0)
Index Cond: ((name)::text = 'name_45'::text)
-> Hash (cost=589756.04..589756.04 rows=976333 width=11)
-> Bitmap Heap Scan on t2 (cost=18289.91..589756.04 rows=976333 width=11)
Recheck Cond: ((name)::text = 'name_45'::text)
-> Bitmap Index Scan on id_name2 (cost=0.00..18045.82 rows=976333 width=0)
Index Cond: ((name)::text = 'name_45'::text)
(11 rows)
jscott=# explain analyze select * from (select * from t1 where t1.name = 'name_45') ft1 inner join (select * from t2 where t2.name = 'name_45') ft2 on ft1.id = ft2.id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=625394.66..1217041.24 rows=9633 width=22) (actual time=40683.719..91322.906 rows=1009521 loops=1)
Hash Cond: (t1.id = t2.id)
-> Bitmap Heap Scan on t1 (cost=18666.46..588242.71 rows=996533 width=11) (actual time=222.936..49785.686 rows=1009335 loops=1)
Recheck Cond: ((name)::text = 'name_45'::text)
Rows Removed by Index Recheck: 83071906
-> Bitmap Index Scan on idx_name (cost=0.00..18417.32 rows=996533 width=0) (actual time=221.901..221.901 rows=1009335 loops=1)
Index Cond: ((name)::text = 'name_45'::text)
-> Hash (cost=589756.04..589756.04 rows=976333 width=11) (actual time=40457.488..40457.488 rows=1009335 loops=1)
Buckets: 4096 Batches: 64 Memory Usage: 691kB
-> Bitmap Heap Scan on t2 (cost=18289.91..589756.04 rows=976333 width=11) (actual time=161.468..40006.936 rows=1009335 loops=1)
Recheck Cond: ((name)::text = 'name_45'::text)
Rows Removed by Index Recheck: 83071906
-> Bitmap Index Scan on id_name2 (cost=0.00..18045.82 rows=976333 width=0) (actual time=160.487..160.487 rows=1009335 loops=1)
Index Cond: ((name)::text = 'name_45'::text)
Total runtime: 91372.419 ms
(15 rows)
jscott=# explain analyze select * from t1 where name = 'name_42';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=19423.56..584967.11 rows=1036933 width=11) (actual time=234.511..13678.203 rows=1008852 loops=1)
Recheck Cond: ((name)::text = 'name_42'::text)
Rows Removed by Index Recheck: 83072451
-> Bitmap Index Scan on idx_name (cost=0.00..19164.32 rows=1036933 width=0) (actual time=233.541..233.541 rows=1008852 loops=1)
Index Cond: ((name)::text = 'name_42'::text)
Total runtime: 13736.464 ms
(6 rows)
Raw
id indexes
jscott=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+------------------------+-----------
id | integer |
name | character varying(255) |
Indexes:
"id_idx" btree (id)
jscott=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+------------------------+-----------
id | integer |
name | character varying(255) |
Indexes:
"id_idx2" btree (id)
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id;
QUERY PLAN
---------------------------------------------------------------------------------------
Merge Join (cost=13970.29..4493686.42 rows=101000000 width=22)
Merge Cond: (t1.id = t2.id)
-> Index Scan using id_idx on t1 (cost=0.00..3171218.83 rows=101000000 width=11)
-> Index Scan using id_idx2 on t2 (cost=0.00..3168700.33 rows=101000000 width=11)
(4 rows)
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id WHERE t1.id = 23942;
QUERY PLAN
--------------------------------------------------------------------------
Nested Loop (cost=0.00..38.70 rows=1 width=22)
-> Index Scan using id_idx on t1 (cost=0.00..19.34 rows=1 width=11)
Index Cond: (id = 23942)
-> Index Scan using id_idx2 on t2 (cost=0.00..19.34 rows=1 width=11)
Index Cond: (id = 23942)
(5 rows)
^
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id WHERE t1.name = 'name_45';
QUERY PLAN
---------------------------------------------------------------------------------------
Merge Join (cost=13970.29..3493698.37 rows=996533 width=22)
Merge Cond: (t1.id = t2.id)
-> Index Scan using id_idx on t1 (cost=0.00..3423718.83 rows=996533 width=11)
Filter: ((name)::text = 'name_45'::text)
-> Index Scan using id_idx2 on t2 (cost=0.00..3168700.33 rows=101000000 width=11)
(5 rows)
jscott=# explain select * from t1 inner join t2 on t1.id = t2.id WHERE t2.name = 'name_45';
QUERY PLAN
--------------------------------------------------------------------------------------
Merge Join (cost=1933758.50..2012663.16 rows=939300 width=22)
Merge Cond: (t1.id = t2.id)
-> Index Scan using id_idx on t1 (cost=0.00..3171218.83 rows=101000000 width=11)
-> Materialize (cost=1933739.32..1938435.82 rows=939300 width=11)
-> Sort (cost=1933739.32..1936087.57 rows=939300 width=11)
Sort Key: t2.id
-> Seq Scan on t2 (cost=0.00..1808446.00 rows=939300 width=11)
Filter: ((name)::text = 'name_45'::text)
(8 rows)
jscott=# explain select * from t1 inner join (select * from t2 where t2.name = 'name_45') ft2 on t1.id = ft2.id;
QUERY PLAN
--------------------------------------------------------------------------------------
Merge Join (cost=1933758.50..2012663.16 rows=939300 width=22)
Merge Cond: (t1.id = t2.id)
-> Index Scan using id_idx on t1 (cost=0.00..3171218.83 rows=101000000 width=11)
-> Materialize (cost=1933739.32..1938435.82 rows=939300 width=11)
-> Sort (cost=1933739.32..1936087.57 rows=939300 width=11)
Sort Key: t2.id
-> Seq Scan on t2 (cost=0.00..1808446.00 rows=939300 width=11)
Filter: ((name)::text = 'name_45'::text)
(8 rows)
jscott=# explain select * from (select * from t1 where t1.name = 'name_45') ft1 inner join (select * from t2 where t2.name = 'name_45') ft2 on ft1.id = ft2.id;
QUERY PLAN
-----------------------------------------------------------------------------------
Merge Join (cost=1933758.50..2003409.46 rows=9268 width=22)
Merge Cond: (t1.id = t2.id)
-> Index Scan using id_idx on t1 (cost=0.00..3423718.83 rows=996533 width=11)
Filter: ((name)::text = 'name_45'::text)
-> Materialize (cost=1933739.32..1938435.82 rows=939300 width=11)
-> Sort (cost=1933739.32..1936087.57 rows=939300 width=11)
Sort Key: t2.id
-> Seq Scan on t2 (cost=0.00..1808446.00 rows=939300 width=11)
Filter: ((name)::text = 'name_45'::text)
(9 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment