On 10/29/21 15:32, Hayk Manukyan wrote:
Hi all First of all thank you all for fast and rich responses, that is really nice.I don't have that deep knowledge of how postgres works under the hood so I will try to explain more user side.I want to refer for some points mentioned above.- First INCLUDE statement mostly eliminates the necessity to refer to a clustered index or table to get columns that do not exist in the index. So filtering upon columns in INCLUDE statement will not be performant. It can give some very little performance if we include additional columns but it is not in level to compare with indexed one. I believe this not for this case - Tomas Vondra's Assumption that adaptive should be something between this two1) (job, nlp, year, sequence) 2) (job, nlp, year, scan_id, issue_flag, sequence)is completely valid. I have made fairly small demo with this index comparison and as I can see the difference is noticeable. Here is git repo and results <https://github.com/HaykManukyanAvetiky/index_comparition/blob/main/results.md> , I had no much time to do significant one sorry for that ))
I find those results entirely unconvincing, or maybe even suspicious.
I used the script to create the objects, and the index sizes are:
Name | Size
------------------------------------------+---------
job_nlp_year_scan_id_issue_flag_sequence | 1985 MB
job_nlp_year_sequence | 1985 MB
So there's no actual difference, most likely due to alignment making up
for the two smalling columns.
And if I randomize the queries instead of running them with the same parameters over and over (see the attached scripts), then an average of 10 runs, each 60s long, the results are (after a proper warmup)
pgbench -n -f q4.sql -T 60 4 columns: 106 ms 6 columns: 109 msSo there's like 3% difference between the two cases, and even that might be just noise. This is consistent with the two indexes being about the same size.
This is on machine with i5-2500k CPU and 8GB of RAM, which is just enough to keep everything in RAM. It seems somewhat strange that your machine does this in 10ms, i.e. 10x faster. Seems strange.
I'm not sure what is the point of the second query, considering it's not even using an index but parallel seqscan.
Anyway, this still fails to demonstrate any material difference between the two indexes, and consequently any potential benefit of the proposed new index type.
regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
q6.sql
Description: application/sql
q4.sql
Description: application/sql
