I'm using PostgreSQL 9.5 Beta 2.
I am working with a partitioned table set.
The first thing I noticed, when creating indexes on the 20 or so partitions, was that if I create them too fast they don't all succeed. I have to do a few at a time, let them breathe for a few seconds, and then do a few more. I had been simply generating all of the create index commands in a text editor, and then cutting and pasting the lot of them into psql all at once or running them by using psql '-f'. Most would get created, but not all. It seems almost random. There were no obvious error messages. When I do a few at a time, it is never an issue.
This tripped me up because I couldn't figure out why some of the child tables were sequence scanning and some were not. It turned out that some of the partitions were missing some of the indexes. I'm mentioning it here just in case someone else is observing strange behaviour where some children are scanning and some aren't. You might not have all of your indexes deployed correctly.
--
Anyway, the issue I am trying to figure out at the moment:
If I do a simple query with a where clause on a specific column from the parent table, I can see it index scan each of the children. This is what I want it to do, so no complaints there.
However, if I try to (inner) join another table with that column, the planner sequence scans each of the children instead of using the indexes. I saw someone had posted a similar question to this list back in January, however I didn't see the answer.
What should I look at to try to figure out why a join doesn't use the indexes while a straight query on the same column for the table does?
FWIW, the column in question is a UUID column and is the primary key for each of the child tables.
--
Rick.