Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele@xxxxxxxxxxxxxxx | -- Fortunato Depero, 1929.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
