Hi Bala, Are your statistics updated? Also, have you used the gin operator(gin_similarity_ops) correctly?
It is fetching just 6 records out of a million, hence, it should not go for bitmap index scan. As bitmap index scan loads a complete index, and access relevant pages from the table later by bitmap heap scan. Regards, Ninad Shah On Thu, 2 Sept 2021 at 16:39, balasubramanian c r <crbs.sie...@gmail.com> wrote: > Hi Team > > We have encountered a problem in our testing environment. > I have a scenario where I am running a similarity match for an address > I have created a table with following number of records > 1603423 > > We are using pg_similarity extension in postgresql version is 13. > > And I have created GIN index (since i am using pg_similarity) library > jaccard similarity method > when I run the Explain analyze > EXPLAIN ANALYZE select complete_address, jaccard(complete_address, 'raj > nagar ext near ajnara integrity up ghaziabad 201017') as qsim from address > where complete_address~??'raj nagar ext near ajnara integrity up ghaziabad > 201017' order by qsim DESC; > QUERY PLAN > > Sort (cost=5856.13..5860.14 rows=1603 width=93) (actual > time=12101.194..12101.197 rows=6 loops=1) > > Sort Key: (jaccard(complete_address, 'raj nagar ext near ajnara integrity > up ghaziabad 201017'::text)) DESC > Sort Method: quicksort Memory: 25kB > -> Bitmap Heap Scan on address (cost=172.43..5770.80 rows=1603 width=93) > (actual time=3516.233..12101.172 rows=6 loops=1) > Recheck Cond: (complete_address ~?? 'raj nagar ext near ajnara integrity > up ghaziabad 201017'::text) > Rows Removed by Index Recheck: 1039186 > Heap Blocks: exact=58018 > -> Bitmap Index Scan on address_complete_address_idx1 (cost=0.00..172.02 > rows=1603 width=0) (actual time=256.037..256.037 rows=1039192 loops=1) > Index Cond: (complete_address ~?? 'raj nagar ext near ajnara integrity up > ghaziabad 201017'::text) > Planning Time: 0.141 ms > Execution Time: 12101.245 ms > (11 rows) > it took 12 seconds > following are my pgconf file settings > shared buffer as 4GB > work_mem 256 MB > maintenence_work_mem 512MB > autovacuum_work_mem 20MB > My index definition is this "address_complete_address_idx1" gin > (complete_address gin_similarity_ops) > > It is taking the index correctly. But why it took 12 seconds to process I > really don't understand. > > Please help. > > Thanks > C.R.Bala >
