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.e...@gmail.com>: > вт, 2 нояб. 2021 г. в 16:04, Hayk Manukyan <manukya...@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 <http://www.postgrespro.com> >
