Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

HI Ninad

Thanks for your reply.
If bitmap index should not be used. Do i need to disable it for the time
being and carry out the test.

The documentation in pg_similarity shows that index can be created on text
column using gin with gin_similarity_ops.
The same way the index is created like
CREATE INDEX on address using GIN(complete_address gin_similarity_ops);

AFAIK I have not seen any other operators other than gin in the
pg_smilarity extension.

Thanks
C.R.Bala

On Thu, Sep 2, 2021 at 8:17 PM Ninad Shah <nshah(dot)postgres(at)gmail(dot)com> wrote:

> 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(dot)siebel(at)gmail(dot)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
>>
>