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 i
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? For me, everything
On 9/19/16 2:29 AM, Knels, Udo wrote:
The difference is that V9.1 uses Nested Loop and the index table_a_pk.
V9.2 and higher don’t use the index.
First thing I'd try is running a manual ANALYZE; on the upgraded
database; the 9.2 plan you showed seems to be using default values, so
it thinks i
On 9/12/16 1:05 PM, phb07 wrote:
The drawback is the overhead of this added ANALYZE statement. With a
heavy processing like in this test case, it is worth to be done. But for
common cases, it's a little bit expensive.
You could always look at the number of rows affected by a command and
make a
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 pl
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 to
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
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.
The partition constraint is in the query criteria. We have non overlappin
