Re: Feature request for adoptive indexes - Mailing list pgsql-hackers

From Hayk Manukyan
Subject Re: Feature request for adoptive indexes
Date
Msg-id CAF+kZOEgw0pKYcyC2z0RSgEE9UHAqOL+5NvLjioog_KZCNxq=Q@mail.gmail.com
Whole thread Raw
In response to Re: Feature request for adoptive indexes  (Pavel Borisov <pashkin.elfe@gmail.com>)
List pgsql-hackers
Hi All

I did final research and saw that the difference between best and worst cases is indeed really small.
I want to thank you guys for your time and efforts.

Best regards.


вт, 2 нояб. 2021 г. в 18:04, Pavel Borisov <pashkin.elfe@gmail.com>:
вт, 2 нояб. 2021 г. в 16:04, Hayk Manukyan <manukyantt@gmail.com>:
Tomas Vondra
> Are you suggesting those are not the actual best/worst cases and we
> should use some other indexes? If yes, which ones?

I would say yes.
In my case I am not querying only sequence column.
I have the following cases which I want to optimize.
1. Select * from Some_table where job = <somthing> and nlp = <something> and year = <something> and  scan_id = <something>  
2. Select * from Some_table where job = <somthing> and nlp = <something> and year = <something> and  Issue_flag = <something>  
3. Select * from Some_table where job = <somthing> and nlp = <something> and year = <something> and  sequence = <something>  
Those are queries that my app send to db that is why I said that from read perspective our best case is 3 separate indexes for 
 (job, nlp, year, sequence) AND (job, nlp, year, Scan_ID) and (job, nlp, year,  issue_flag)  and any other solution like 
 (job, nlp, year, sequence, Scan_ID, issue_flag) OR  (job, nlp, year ) INCLUDE(sequence, Scan_ID, issue_flag)  OR just (job, nlp, year) can be considered as worst case 
I will remind that in real world scenario I have ~50m rows and about ~5k rows for each (job, nlp, year )

 So you get 50M rows /5K rows = 10K times selectivity, when you select on job = <somthing> and nlp = <something> and year = <something> which is enormous. Then you should select some of the 5K rows left, which is expected to be pretty fast on bitmap index scan or INCLUDE column filtering. It confirms Tomas's experiment 

  pgbench -n -f q4.sql -T 60

106 ms vs 109 ms

fits your case pretty well. You get absolutely negligible difference between best and worst case and certainly you don't need anything more than just plain index for 3 columns, you even don't need INCLUDE index.

From what I read I suppose that this feature indeed doesn't based on the real need. If you suppose it is useful please feel free to make and post here some measurements that proves your point.




--
Best regards,
Pavel Borisov

Postgres Professional: http://postgrespro.com

pgsql-hackers by date:

Previous
From: Andrey Borodin
Date:
Subject: Re: Why doesn't GiST VACUUM require a super-exclusive lock, like nbtree VACUUM?
Next
From: Isaac Morland
Date:
Subject: Re: [PATCH] rename column if exists