Re: [PERFORM] query against single partition uses index, against master table does seq scan

Mike Broers writes: > This is 9.5, sorry I didnt mention that in the initial post. Hmm, that's odd then. > I am guessing the issue is that the secondary non-indexed criteria is a > search through a jsonb column? Doubt it; it should have considered the plan you are thinking

Re: [PERFORM] query against single partition uses index, against master table does seq scan

This is 9.5, sorry I didnt mention that in the initial post. I am guessing the issue is that the secondary non-indexed criteria is a search through a jsonb column? Let me know if I can provide any additional info, as I stated I am working around it with a subquery at the moment. This seems like

Re: [PERFORM] query against single partition uses index, against master table does seq scan

Mike Broers writes: > Hello, I am curious about the performance of queries against a master table > that seem to do seq scans on each child table. When the same query is > issued at a partition directly it uses the partition index and is very > fast. What PG version is that?

Re: [PERFORM] query against single partition uses index, against master table does seq scan

When I excluded the non indexed search criteria the query on aggregate used the indexes on each partition, without specifying the constraint exclusion criteria. When I added the constraint exclusion criteria to the non indexed criteria, it still used seq scans. I ended up getting an acceptable

Re: [PERFORM] query against single partition uses index, against master table does seq scan

Thanks for your response - Is 'selectively choosing what partition' different than utilizing each partitions index when scanning each partition? To clarify, I expect to find results in each partition, but to have postgres use each partitions index instead of full table scans. It seems redundant

Re: [PERFORM] query against single partition uses index, against master table does seq scan

Postgres does not have capability to selectively choose child tables unless the query's "WHERE" clause is simple, and it matches (exactly) the CHECK constraint definition. I have resolved similar issue by explicitly adding check constraint expression in every SQL against the master table. This