Timeline for Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?
Current License: CC BY-SA 3.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 6, 2015 at 12:19 | comment | added | ypercubeᵀᴹ | @CraigRinger thnx for clarifying this. | |
| Aug 6, 2015 at 12:18 | comment | added | Craig Ringer | @ypercube an index-only scan does not touch the table. False. It might not touch the table, but only if the visibility map shows that all heap pages are 100% visible for the scanned index leaves. | |
| Aug 4, 2015 at 19:31 | vote | accept | Chris Tien | ||
| Aug 4, 2015 at 17:01 | comment | added | ypercubeᵀᴹ | I can't so you are probably right. It's either a bug or they are not entirely correct in the documentation and index-only scans can (or have to) in some cases use the table instead of the visibility map (or both). | |
| Aug 4, 2015 at 16:18 | history | edited | András Váczi | CC BY-SA 3.0 | added 202 characters in body |
| Aug 4, 2015 at 16:15 | comment | added | Chris Tien | @ypercube if the index-only-scan does not touch heaps then why there are lines under index-only-scan said "heap fetches: XXXX(number of tuples of the scanned table)" in the result of Explain Analyze Query ? | |
| Aug 4, 2015 at 16:14 | comment | added | András Váczi | Hm, now I found an excellent post from Robert Haas, where he describes these in detail. I have to admit you are right - index-only scans are not always index-only, but they try as long as it is possible. At the same time, an index-only scan with additional heap fetches is possible when a sufficient portion of the table pages remains unchanged since the last vacuum. | |
| Aug 4, 2015 at 16:02 | comment | added | ypercubeᵀᴹ | @ChrisTien an index only scan does not touch the table. | |
| Aug 4, 2015 at 15:31 | comment | added | Chris Tien | maybe u need do experiments to verify your thoughts, use "explain analyze verbose YOUR QUERY" | |
| Aug 4, 2015 at 14:20 | comment | added | Chris Tien | @dezso However, after i vacuum all tables involved in the query, the heap fetches become zero, which means index-only-scan doesn't touch heaps this time. So we can draw the conclusion, index-only-scan will still try to fetch data from table in some case, it depends on the visibility map, when u vacuum your tables, the visibility map is set to visible to all users which can result in best performance of index-only-scan. | |
| Aug 4, 2015 at 14:14 | comment | added | Chris Tien | @dezso I know the index-only-scan is designed to not fetch data from heaps. In my experiments, I let index-only-scan be the only one on (turn off seq-scan and index-scan), then I explain analyze my query, it shows that it indeed use index-only-scan, but the heap fetches is quite high, equal to the number of tuples in scanned table. | |
| Aug 4, 2015 at 5:48 | history | answered | Chris Tien | CC BY-SA 3.0 |