Timeline for Speed up creation of Postgres partial index
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 11, 2023 at 8:43 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
typo, markup, clarify
|
| Aug 17, 2019 at 0:17 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
Bring best option to the top, clean up
|
| Jun 19, 2019 at 16:10 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
update links, quote and partitioning
|
| May 8, 2017 at 18:55 | vote | accept | burnsy | ||
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot |
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
|
|
| Aug 25, 2015 at 14:31 | comment | added | Erwin Brandstetter | @Chris: I agree, 5 indexes will take longer to create than just one (even if all of them together are smaller, creating each index is cheaper and queries may be faster). Thinking about it some more, this should be a perfect use case for a BRIN index in Postgres 9.5. | |
| Aug 25, 2015 at 14:27 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
add BRIN index
|
| Aug 25, 2015 at 3:33 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
update after Q update and comments
|
| Aug 24, 2015 at 19:26 | comment | added | Chris | I'm with Jurgen on this one. I believe that due to the size of the table, in essence you have to perform a full sequential scan on the table per index created. Plus, I'm not sure you'll see all that much performance increase from creating the separate partial indexes (I'm 90% sure you won't see any increase, but on this I could be off.) I believe a better solution for index creation would involve creating one index over the full range that you wish to query as a "single partial index" to keep the overall build time down. | |
| Aug 24, 2015 at 18:25 | comment | added | Jürgen Strobel | I bet his work is IO bound since the table is much larger than RAM. Reading the table even more often will make the problem worse, regardless if there's enough memory to sort each created index in memory or not. | |
| May 26, 2015 at 22:34 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
added 496 characters in body
|
| May 26, 2015 at 22:28 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
added 496 characters in body
|
| May 26, 2015 at 22:14 | history | answered | Erwin Brandstetter | CC BY-SA 3.0 |