Re: Understanding "seq scans"

On Monday, October 12, 2015, Lele Gaifax <lele(at)metapensiero(dot)it> wrote:

> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com <javascript:;>> 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.629 rows=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.946
> rows=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=9999999 loops=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.
> --
>

Your data and indexes are organized such that an index is only marginally
helpful, or so the planner thinks. Try:

1. Cranking effective_cache_size so the planner might think your data is
cached.

2. Reducing random_page_cost to discourage random plans

3. Temporarily disabling seq scans

4. Composite index for better lookups.

merlin