Re: Feature request for adoptive indexes

вт, 2 нояб. 2021 г. в 16:04, Hayk Manukyan <manukyantt(at)gmail(dot)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>