Understanding "seq scans" - Mailing list 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@metapensiero.it  |                 -- Fortunato Depero, 1929.