From: | Lele Gaifax <lele(at)metapensiero(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Understanding "seq scans" |
Date: | 2015-10-12 19:06:40 |
Message-ID: | 87y4f7dhz3.fsf@metapensiero.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I'm doing some experiments to find the better layout for reimplementing
an existing db (MySQL cough!) with PostgreSQL 9.4+.
I noticed a strange plan coming out from a simple query joining two tables,
both containing 10Mrecs (and both ANALYZEd):
l10ntest=# \d master;
Table "public.master"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
num | integer | not null default nextval('master_num_seq'::regclass)
Indexes:
"master_pkey" PRIMARY KEY, btree (num)
l10ntest=# \d master_l10n;
Table "public.master_l10n"
Column | Type | Modifiers
--------+----------------------+-----------------------------------------------------------
num | integer | not null default nextval('master_l10n_num_seq'::regclass)
lang | character varying(2) | not null
text | text |
Indexes:
"master_l10n_pkey" PRIMARY KEY, btree (num, lang)
"l10n_text_index" btree (lower(text) text_pattern_ops)
l10ntest=# EXPLAIN SELECT count(l.num) AS count_1 FROM master_l10n l WHERE l.lang = 'it' AND lower(l.text) LIKE 'quattro%';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=309315.38..309315.39 rows=1 width=4)
-> Bitmap Heap Scan on master_l10n l (cost=64700.56..307801.65 rows=605492 width=4)
Filter: (((lang)::text = 'it'::text) AND (lower(text) ~~ 'quattro%'::text))
-> Bitmap Index Scan on l10n_text_index (cost=0.00..64549.19 rows=999662 width=0)
Index Cond: ((lower(text) ~>=~ 'quattro'::text) AND (lower(text) ~<~ 'quattrp'::text))
(5 rows)
Time: 1.665 ms
l10ntest=# EXPLAIN SELECT count(m.num) AS count_1 FROM master_l10n l JOIN master m ON m.num = l.num WHERE l.lang = 'it' AND lower(l.text) LIKE 'quattro%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Aggregate (cost=676558.14..676558.15 rows=1 width=4)
-> Hash Join (cost=373011.02..675044.41 rows=605492 width=4)
Hash Cond: (l.num = m.num)
-> Bitmap Heap Scan on master_l10n l (cost=64700.56..307801.65 rows=605492 width=4)
Filter: (((lang)::text = 'it'::text) AND (lower(text) ~~ 'quattro%'::text))
-> Bitmap Index Scan on l10n_text_index (cost=0.00..64549.19 rows=999662 width=0)
Index Cond: ((lower(text) ~>=~ 'quattro'::text) AND (lower(text) ~<~ 'quattrp'::text))
-> Hash (cost=144247.76..144247.76 rows=9999976 width=4)
-> Seq Scan on master m (cost=0.00..144247.76 rows=9999976 width=4)
(9 rows)
Time: 1.244 ms
l10ntest=# SELECT count(l.num) AS count_1 FROM master_l10n l WHERE l.lang = 'it' AND lower(l.text) LIKE 'quattro%';
count_1
---------
1101101
(1 row)
Time: 1221.941 ms
l10ntest=# SELECT count(m.num) AS count_1 FROM master_l10n l JOIN master m ON m.num = l.num WHERE l.lang = 'it' AND lower(l.text) LIKE 'quattro%';
count_1
---------
1101101
(1 row)
Time: 3541.852 ms
Why does the join on the master table require a "Seq Scan on master"? I tried
different kinds of "JOIN", but the resulting explanation remains the same.
Am I missing something, or should I stop worrying about that sequential scan?
Thanks in advance,
bye, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele(at)metapensiero(dot)it | -- Fortunato Depero, 1929.
From | Date | Subject | |
---|---|---|---|
Next Message | Israel Brewster | 2015-10-12 19:07:08 | Re: Pattern match against array elements? |
Previous Message | Israel Brewster | 2015-10-12 19:05:21 | Re: Pattern match against array elements? |