I don't know about partial indexing, but in a similar situation to what you describe, I have resorted to special tables designed specifically to hold one day's worth of data and to support our daily processing. I'd put the daily data into specific tables, with all the requisite indeces, and then, as part of the daily processing and after it has been processed, copy the day's data into the main tables and empty the tables used to hold the data temporarily. It may not be elegant or pretty, but it serves to greatly simplify processing since you know a priori that these tables content only the data you need to process, and you can be certain that eventually it finds its way into the main data tables. And you have your indeces only on the tables used for daily processing, rather than on your main tables. An additional concern I had was that if the process for getting the data is vulnerable to error (and in my case it was), you can apply your
verification procedures to it before bad data gets into your main tables, thereby reducing the risk of bad data getting into the database.
I though this might be an option for you if you have trouble getting your partial indeces to work well for you.
HTH
Ted
Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote:
Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote:
Does PostgreSQL use an existing index, if possible, when creating a
partial index?
By way of background, we have some nightly bulk processing that includes
a couple of 15-30 million row tables. Most of the processing is only
looking at prior-day data (up to ~200,000 rows) and for efficiency
requires several indexes. Except for this one process, the indexes are
useless and I'd rather not constantly maintain them.
