Re: Understanding "seq scans" - Mailing list pgsql-general

Adrian Klaver <adrian.klaver@aklaver.com> writes:

> Off hand I would say it is because of this --> count(m.num). Try count(l.num) instead and see
> what happens. As your queries above show they are the same number.

No, that's another thing I already tried tweaking and should have mentioned.
Neither count(*) nor count(l.num) have any influence on the plan.

Following Kevin's advice (thanks!) I read the SlowQueryQuestions wiki page and
learned about the "buffers" EXPLAIN option:

    EXPLAIN (analyze,buffers) SELECT count(l.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) (actual time=4133.991..4133.991 rows=1 loops=1)
       Buffers: shared hit=6 read=84710, temp read=32652 written=32398
       ->  Hash Join  (cost=373011.02..675044.41 rows=605492 width=4) (actual time=1940.285..4074.654 rows=1101101
loops=1)
             Hash Cond: (l.num = m.num)
             Buffers: shared hit=6 read=84710, temp read=32652 written=32398
             ->  Bitmap Heap Scan on master_l10n l  (cost=64700.56..307801.65 rows=605492 width=4) (actual
time=201.132..1286.629rows=1101101 loops=1) 
                   Filter: (((lang)::text = 'it'::text) AND (lower(text) ~~ 'quattro%'::text))
                   Heap Blocks: exact=25621
                   Buffers: shared hit=1 read=40464
                   ->  Bitmap Index Scan on l10n_text_index  (cost=0.00..64549.19 rows=999662 width=0) (actual
time=195.946..195.946rows=1101101 loops=1) 
                         Index Cond: ((lower(text) ~>=~ 'quattro'::text) AND (lower(text) ~<~ 'quattrp'::text))
                         Buffers: shared read=14844
             ->  Hash  (cost=144247.76..144247.76 rows=9999976 width=4) (actual time=1738.180..1738.180 rows=9999999
loops=1)
                   Buckets: 16384  Batches: 128  Memory Usage: 2778kB
                   Buffers: shared hit=2 read=44246, temp written=29000
                   ->  Seq Scan on master m  (cost=0.00..144247.76 rows=9999976 width=4) (actual time=0.006..629.590
rows=9999999loops=1) 
                         Buffers: shared hit=2 read=44246
     Planning time: 0.493 ms
     Execution time: 4134.144 ms
    (19 rows)

    # select version();
                                                    version
    --------------------------------------------------------------------------------------------------------
     PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 5.2.1-21) 5.2.1 20151003, 64-bit
    (1 row)

Thank you,
ciao, 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.