0

I am not extremely familiar with the process that takes place when an index is created on a table. However, if I have partitioned tables that have indexes on them and had an index on the master table; if I drop the index on the master table in hopes that the query planner only scans partitioned tables in the date range specified (tables are partitioned by year and month) and I run a query to get data for a range, the query planner is still running an index scan on all the partitioned tables instead of just the ones in the range specified.

Does dropping the index from the table also remove the indexes from the data? If I drop the index on the master table, should that not fix the partition elimination issues I am currently having?

John B
  • 159
  • 3
  • 14
  • If you drop the index, the index is gone. I don't understand the "*remove the index from the data*". The indexed data is stored _in_ the index. Partition pruning is based on check constraints defined on the master table, not on the existence of an index. If you have a problem with the execution plan, show us the table definitions, the index definitions and the output of `explain analyze`. See here for more details: http://stackoverflow.com/tags/postgresql-performance/info –  Aug 05 '15 at 16:04
  • Are you wanting to force the query optimiser to work a certain way (i.e. hints?) - if so you're out of luck, Postgres doesn't do them, though this question may have some advice http://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index – Matt Allwood Aug 05 '15 at 16:48
  • Have you read the manual on PostgreSQL's (rudimentary) partitioning support, in particular the section on constraint exclusion? – Craig Ringer Aug 05 '15 at 23:16

0 Answers0