On Monday, October 12, 2015, Lele Gaifax <l...@metapensiero.it> wrote:
> Adrian Klaver <adrian.kla...@aklaver.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
