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

[Mike Broers]

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 it may become a
more frequent ad-hoc need so if there is something else I can do it would
be appreciated.

On Wed, Sep 21, 2016 at 9:11 PM, Tom Lane  wrote:

> 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 since 9.0 seems to be willing
> to consider the type of plan you're expecting.
>
> regards, tom lane
>

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

[Tom Lane]

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 since 9.0 seems to be willing
to consider the type of plan you're expecting.

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher

[Jim Nasby]

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 it's going to get 100 rows when it's only getting a few.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Strange nested loop for an INSERT

[Jim Nasby]

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 decision on whether to ANALYZE based on that, possibly by looking 
at pg_stat_all_tables.n_mod_since_analyze.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

[Mike Broers]

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 plan by using a subquery on the indexed
partition and using those results to scan for the unindexed value.

On Wed, Sep 21, 2016 at 12:37 PM, Mike Broers  wrote:

> 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 add a where clauses to match each exclusion criteria but
> i will try that and report back - thank you for the suggestion.
>
> On Wed, Sep 21, 2016 at 12:15 PM, Ganesh Kannan  weatheranalytics.com> wrote:
>
>> 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 is also determined by the constraint_exclusion setting value. Check
>> the manual (9.5): https://www.postgresql.org/docs/current/static/ddl-pa
>> rtitioning.html.
>>
>>
>> I would try tweaking WHERE clause to match Check constraint definition.
>> Global partitioning index (like in Oracle) would help, but its just my wish.
>>
>>
>>
>> Regards,
>> Ganesh Kannan
>>
>>
>>
>> --
>> *From:* pgsql-performance-ow...@postgresql.org <
>> pgsql-performance-ow...@postgresql.org> on behalf of Mike Broers <
>> mbro...@gmail.com>
>> *Sent:* Wednesday, September 21, 2016 12:53 PM
>> *To:* pgsql-performance@postgresql.org
>> *Subject:* [PERFORM] query against single partition uses index, against
>> master table does seq scan
>>
>> 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
>> overlapping check constraints and constraint exclusion is set to partition.
>>
>> Here is the master table
>>   Column  Type
>>Modifiers
>> aggregate_id   bigint  not null default
>> nextval('seq_aggregate'::regclass)
>> landing_id integer not null
>> client_program_id  integer
>> sequence_numberbigint
>> start_datetime timestamp without time zone not null
>> end_datetime   timestamp without time zone not null
>> body   jsonb   not null
>> client_parsing_status_code character(1)
>> validation_status_code character(1)
>> client_parsing_datetimetimestamp without time zone
>> validation_datetimetimestamp without time zone
>> latest_flag_datetime   timestamp without time zone
>> latest_flagboolean not null
>> Indexes:
>> "pk_aggregate" PRIMARY KEY, btree (aggregate_id)
>> "ix_aggregate_landing_id_aggregate_id_parsing_status" btree
>> (landing_id, aggregate_id, client_parsing_status_code)
>> "ix_aggregate_landing_id_start_datetime" btree (landing_id,
>> start_datetime)
>> "ix_aggregate_latest_flag" btree (latest_flag_datetime) WHERE
>> latest_flag = false
>> "ix_aggregate_validation_status_code" btree (validation_datetime)
>> WHERE validation_status_code = 'P'::bpchar AND latest_flag = true
>> Check constraints:
>> "ck_aggregate_client_parsing_status_code" CHECK
>> (client_parsing_status_code IS NULL OR (client_parsing_status_code = ANY
>> (ARRAY['P'::bpchar, 'F'::bpchar, 'I'::bpchar])))
>> "ck_aggregate_validation_status_code" CHECK (validation_status_code
>> IS NULL OR (validation_status_code = ANY (ARRAY['P'::bpchar, 'F'::bpchar,
>> 'I'::bpchar])))
>> Foreign-key constraints:
>> "fk_aggregate_client_program" FOREIGN KEY (client_program_id)
>> REFERENCES client_program(client_program_id)
>> "fk_aggregate_landing" FOREIGN KEY (landing_id) REFERENCES
>> landing(landing_id)
>> Number of child tables: 17 (Use \d+ to list them.)
>>
>> and here is a child table showing a check constraint
>>  Table "stage.aggregate__7223"
>>   Column  Type
>>Modifiers
>> ── ───
>> aggregate_id   bigint  not null default
>> nextval('seq_aggregate'::regclass)
>> landing_id integer not null
>> client_program_id  integer
>> 

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

[Mike Broers]

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 add a where clauses to match each exclusion criteria but
i will try that and report back - thank you for the suggestion.

On Wed, Sep 21, 2016 at 12:15 PM, Ganesh Kannan <
ganesh.kan...@weatheranalytics.com> wrote:

> 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 is also determined by the constraint_exclusion setting value. Check
> the manual (9.5): https://www.postgresql.org/docs/current/static/ddl-
> partitioning.html.
>
>
> I would try tweaking WHERE clause to match Check constraint definition.
> Global partitioning index (like in Oracle) would help, but its just my wish.
>
>
>
> Regards,
> Ganesh Kannan
>
>
>
> --
> *From:* pgsql-performance-ow...@postgresql.org  postgresql.org> on behalf of Mike Broers 
> *Sent:* Wednesday, September 21, 2016 12:53 PM
> *To:* pgsql-performance@postgresql.org
> *Subject:* [PERFORM] query against single partition uses index, against
> master table does seq scan
>
> 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
> overlapping check constraints and constraint exclusion is set to partition.
>
> Here is the master table
>   Column  Type
>  Modifiers
> aggregate_id   bigint  not null default
> nextval('seq_aggregate'::regclass)
> landing_id integer not null
> client_program_id  integer
> sequence_numberbigint
> start_datetime timestamp without time zone not null
> end_datetime   timestamp without time zone not null
> body   jsonb   not null
> client_parsing_status_code character(1)
> validation_status_code character(1)
> client_parsing_datetimetimestamp without time zone
> validation_datetimetimestamp without time zone
> latest_flag_datetime   timestamp without time zone
> latest_flagboolean not null
> Indexes:
> "pk_aggregate" PRIMARY KEY, btree (aggregate_id)
> "ix_aggregate_landing_id_aggregate_id_parsing_status" btree
> (landing_id, aggregate_id, client_parsing_status_code)
> "ix_aggregate_landing_id_start_datetime" btree (landing_id,
> start_datetime)
> "ix_aggregate_latest_flag" btree (latest_flag_datetime) WHERE
> latest_flag = false
> "ix_aggregate_validation_status_code" btree (validation_datetime)
> WHERE validation_status_code = 'P'::bpchar AND latest_flag = true
> Check constraints:
> "ck_aggregate_client_parsing_status_code" CHECK
> (client_parsing_status_code IS NULL OR (client_parsing_status_code = ANY
> (ARRAY['P'::bpchar, 'F'::bpchar, 'I'::bpchar])))
> "ck_aggregate_validation_status_code" CHECK (validation_status_code
> IS NULL OR (validation_status_code = ANY (ARRAY['P'::bpchar, 'F'::bpchar,
> 'I'::bpchar])))
> Foreign-key constraints:
> "fk_aggregate_client_program" FOREIGN KEY (client_program_id)
> REFERENCES client_program(client_program_id)
> "fk_aggregate_landing" FOREIGN KEY (landing_id) REFERENCES
> landing(landing_id)
> Number of child tables: 17 (Use \d+ to list them.)
>
> and here is a child table showing a check constraint
>  Table "stage.aggregate__7223"
>   Column  Type
>  Modifiers
> ── ───
> aggregate_id   bigint  not null default
> nextval('seq_aggregate'::regclass)
> landing_id integer not null
> client_program_id  integer
> sequence_numberbigint
> start_datetime timestamp without time zone not null
> end_datetime   timestamp without time zone not null
> body   jsonb   not null
> client_parsing_status_code character(1)
> validation_status_code character(1)
> client_parsing_datetimetimestamp without time zone
> validation_datetimetimestamp without time zone
> latest_flag_datetime   timestamp without time zone
> latest_flagboolean not null
> Indexes:
> "pk_aggregate__7223" PRIMARY 

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

[Ganesh Kannan]

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 is also 
determined by the constraint_exclusion setting value. Check the manual (9.5): 
https://www.postgresql.org/docs/current/static/ddl-partitioning.html.


I would try tweaking WHERE clause to match Check constraint definition. Global 
partitioning index (like in Oracle) would help, but its just my wish.



Regards,
Ganesh Kannan



From: pgsql-performance-ow...@postgresql.org 
 on behalf of Mike Broers 

Sent: Wednesday, September 21, 2016 12:53 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] query against single partition uses index, against master 
table does seq scan

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 overlapping 
check constraints and constraint exclusion is set to partition.

Here is the master table
  Column  Type  
Modifiers
aggregate_id   bigint  not null default 
nextval('seq_aggregate'::regclass)
landing_id integer not null
client_program_id  integer
sequence_numberbigint
start_datetime timestamp without time zone not null
end_datetime   timestamp without time zone not null
body   jsonb   not null
client_parsing_status_code character(1)
validation_status_code character(1)
client_parsing_datetimetimestamp without time zone
validation_datetimetimestamp without time zone
latest_flag_datetime   timestamp without time zone
latest_flagboolean not null
Indexes:
"pk_aggregate" PRIMARY KEY, btree (aggregate_id)
"ix_aggregate_landing_id_aggregate_id_parsing_status" btree (landing_id, 
aggregate_id, client_parsing_status_code)
"ix_aggregate_landing_id_start_datetime" btree (landing_id, start_datetime)
"ix_aggregate_latest_flag" btree (latest_flag_datetime) WHERE latest_flag = 
false
"ix_aggregate_validation_status_code" btree (validation_datetime) WHERE 
validation_status_code = 'P'::bpchar AND latest_flag = true
Check constraints:
"ck_aggregate_client_parsing_status_code" CHECK (client_parsing_status_code 
IS NULL OR (client_parsing_status_code = ANY (ARRAY['P'::bpchar, 'F'::bpchar, 
'I'::bpchar])))
"ck_aggregate_validation_status_code" CHECK (validation_status_code IS NULL 
OR (validation_status_code = ANY (ARRAY['P'::bpchar, 'F'::bpchar, 
'I'::bpchar])))
Foreign-key constraints:
"fk_aggregate_client_program" FOREIGN KEY (client_program_id) REFERENCES 
client_program(client_program_id)
"fk_aggregate_landing" FOREIGN KEY (landing_id) REFERENCES 
landing(landing_id)
Number of child tables: 17 (Use \d+ to list them.)

and here is a child table showing a check constraint
 Table "stage.aggregate__7223"
  Column  Type  
Modifiers
── ───
aggregate_id   bigint  not null default 
nextval('seq_aggregate'::regclass)
landing_id integer not null
client_program_id  integer
sequence_numberbigint
start_datetime timestamp without time zone not null
end_datetime   timestamp without time zone not null
body   jsonb   not null
client_parsing_status_code character(1)
validation_status_code character(1)
client_parsing_datetimetimestamp without time zone
validation_datetimetimestamp without time zone
latest_flag_datetime   timestamp without time zone
latest_flagboolean not null
Indexes:
"pk_aggregate__7223" PRIMARY KEY, btree (aggregate_id), tablespace 
"archive"
"ix_aggregate__7223_landing_id_aggregate_id_parsing_status" btree 
(landing_id, aggregate_id, client_parsing_status_code), tablespace "archive"
"ix_aggregate__7223_landing_id_start_datetime" btree (landing_id, 
start_datetime), tablespace "archive"
"ix_aggregate__7223_latest_flag" btree (latest_flag_datetime) WHERE 
latest_flag = false, tablespace "archive"
"ix_aggregate__7223_validation_status_code" btree (validation_datetime) 
WHERE validation_status_code = 'P'::bpchar AND latest_flag = true, tablespace 
"archive"
Check 

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

[Mike Broers]

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 overlapping
check constraints and constraint exclusion is set to partition.

Here is the master table
  Column  Type
 Modifiers
aggregate_id   bigint  not null default
nextval('seq_aggregate'::regclass)
landing_id integer not null
client_program_id  integer
sequence_numberbigint
start_datetime timestamp without time zone not null
end_datetime   timestamp without time zone not null
body   jsonb   not null
client_parsing_status_code character(1)
validation_status_code character(1)
client_parsing_datetimetimestamp without time zone
validation_datetimetimestamp without time zone
latest_flag_datetime   timestamp without time zone
latest_flagboolean not null
Indexes:
"pk_aggregate" PRIMARY KEY, btree (aggregate_id)
"ix_aggregate_landing_id_aggregate_id_parsing_status" btree
(landing_id, aggregate_id, client_parsing_status_code)
"ix_aggregate_landing_id_start_datetime" btree (landing_id,
start_datetime)
"ix_aggregate_latest_flag" btree (latest_flag_datetime) WHERE
latest_flag = false
"ix_aggregate_validation_status_code" btree (validation_datetime) WHERE
validation_status_code = 'P'::bpchar AND latest_flag = true
Check constraints:
"ck_aggregate_client_parsing_status_code" CHECK
(client_parsing_status_code IS NULL OR (client_parsing_status_code = ANY
(ARRAY['P'::bpchar, 'F'::bpchar, 'I'::bpchar])))
"ck_aggregate_validation_status_code" CHECK (validation_status_code IS
NULL OR (validation_status_code = ANY (ARRAY['P'::bpchar, 'F'::bpchar,
'I'::bpchar])))
Foreign-key constraints:
"fk_aggregate_client_program" FOREIGN KEY (client_program_id)
REFERENCES client_program(client_program_id)
"fk_aggregate_landing" FOREIGN KEY (landing_id) REFERENCES
landing(landing_id)
Number of child tables: 17 (Use \d+ to list them.)

and here is a child table showing a check constraint
 Table "stage.aggregate__7223"
  Column  Type
 Modifiers
── ───
aggregate_id   bigint  not null default
nextval('seq_aggregate'::regclass)
landing_id integer not null
client_program_id  integer
sequence_numberbigint
start_datetime timestamp without time zone not null
end_datetime   timestamp without time zone not null
body   jsonb   not null
client_parsing_status_code character(1)
validation_status_code character(1)
client_parsing_datetimetimestamp without time zone
validation_datetimetimestamp without time zone
latest_flag_datetime   timestamp without time zone
latest_flagboolean not null
Indexes:
"pk_aggregate__7223" PRIMARY KEY, btree (aggregate_id), tablespace
"archive"
"ix_aggregate__7223_landing_id_aggregate_id_parsing_status" btree
(landing_id, aggregate_id, client_parsing_status_code), tablespace "archive"
"ix_aggregate__7223_landing_id_start_datetime" btree (landing_id,
start_datetime), tablespace "archive"
"ix_aggregate__7223_latest_flag" btree (latest_flag_datetime) WHERE
latest_flag = false, tablespace "archive"
"ix_aggregate__7223_validation_status_code" btree
(validation_datetime) WHERE validation_status_code = 'P'::bpchar AND
latest_flag = true, tablespace "archive"
Check constraints:
"ck_aggregate__7223_landing_id" CHECK (landing_id >= 7223 AND
landing_id < 9503)
"ck_aggregate_client_parsing_status_code" CHECK
(client_parsing_status_code IS NULL OR (client_parsing_status_code = ANY
(ARRAY['P'::bpchar, 'F'::bpchar, 'I'::bpchar])))
"ck_aggregate_validation_status_code" CHECK (validation_status_code IS
NULL OR (validation_status_code = ANY (ARRAY['P'::bpchar, 'F'::bpchar,
'I'::bpchar])))
Inherits: aggregate
Tablespace: "archive"

Here is an example of the query explain plan against the master table:

select landing_id from landing L
where exists
(
select landing_id
from stage.aggregate A
WHERE (A.body#>>'{Cost}')::BIGINT >= 10
and L.landing_id = A.Landing_id
)
and L.source_id = 36


Hash Join  (cost=59793745.91..59793775.14 rows=28 width=4)
  Hash Cond: (a.landing_id = l.landing_id)
  ->  HashAggregate  (cost=59792700.41..59792721.46 rows=2105 width=4)
Group Key: a.landing_id
->  Append  (cost=0.00..59481729.32 rows=124388438 width=4)
  ->  Seq Scan on aggregate a  (cost=0.00..0.00