Re: Slow PostgreSQL 10.6 query
Behrang Saeedzadeh writes: > On my machine, this query that is generated by Hibernate runs in about 57 > ms on MySQL 8 but it takes more than 1 second to run on PostgreSQL: > SELECT bills.id AS bill_id, >bills.bill_date AS bill_date, >bills.bill_numberAS bill_number, >branch_bills.branch_id AS branch_id, >company_bills.company_id AS company_id > FROM tbl_bills bills > LEFT OUTER JOIN tbl_branch_bills branch_bills ON bills.id = > branch_bills.bill_id > LEFT OUTER JOIN tbl_company_bills company_bills ON bills.id = > company_bills.bill_id > INNER JOIN tbl_branches ON branch_bills.branch_id = > tbl_branches.id > WHERE branch_bills.branch_id IN ( > SELECT b.id > FROM tbl_branches b > INNER JOIN tbl_rules r ON b.id = r.branch_id > INNER JOIN tbl_groups g ON r.group_id = g.id > INNER JOIN (tbl_group_permissions gp INNER JOIN > tbl_permissions p ON gp.permission_id = p.id) > ON g.id = gp.group_id > INNER JOIN tbl_users u ON r.user_id = u.id > WHERE u.id = 1 > AND r.rule_type = 'BRANCH' > AND p.name = 'Permission W' > ); [ counts the JOINs... ] You might try raising join_collapse_limit and from_collapse_limit to be 12 or so. regards, tom lane
Re: Slow PostgreSQL 10.6 query
Thanks. That eliminated the bottleneck! Any ideas why adding ORDER BY to the subquery also changes the plan in a way that eliminates the bottleneck? Best regards, Behrang Saeedzadeh blog.behrang.org On Tue, 1 Oct 2019 at 23:27, Tom Lane wrote: > Behrang Saeedzadeh writes: > > On my machine, this query that is generated by Hibernate runs in about 57 > > ms on MySQL 8 but it takes more than 1 second to run on PostgreSQL: > > > SELECT bills.id AS bill_id, > >bills.bill_date AS bill_date, > >bills.bill_numberAS bill_number, > >branch_bills.branch_id AS branch_id, > >company_bills.company_id AS company_id > > FROM tbl_bills bills > > LEFT OUTER JOIN tbl_branch_bills branch_bills ON bills.id = > > branch_bills.bill_id > > LEFT OUTER JOIN tbl_company_bills company_bills ON bills.id = > > company_bills.bill_id > > INNER JOIN tbl_branches ON branch_bills.branch_id = > > tbl_branches.id > > WHERE branch_bills.branch_id IN ( > > SELECT b.id > > FROM tbl_branches b > > INNER JOIN tbl_rules r ON b.id = r.branch_id > > > INNER JOIN tbl_groups g ON r.group_id = g.id > > INNER JOIN (tbl_group_permissions gp INNER JOIN > > tbl_permissions p ON gp.permission_id = p.id) > > ON g.id = gp.group_id > > INNER JOIN tbl_users u ON r.user_id = u.id > > WHERE u.id = 1 > > AND r.rule_type = 'BRANCH' > > AND p.name = 'Permission W' > > ); > > [ counts the JOINs... ] You might try raising join_collapse_limit and > from_collapse_limit to be 12 or so. > > regards, tom lane >
pg12 - partition by column that might have null values
Hey,
In PG12 I'm trying to create partitions by range on a date column that
might be null (indicates it is the most recent version of the object). My
PK has to include the partition column, therefore I'm getting an error that
I cant create a primary key with the specific column because it has null
values.
For example :
\d object_revision
Table "public.object_revision"
Column|Type | Collation | Nullable |
Default
-+-+---+--+---
id | integer | | not null |
nextval('mariel_dates_test_id_seq'::regclass)
end_time| timestamp without time zone | | |
object_hash | text| | |
Indexes:
"id_pk" PRIMARY KEY, btree (id)
Lets say that the same object (object_hash) can have many revisions, the
end_time is the time it was last updated. I'm trying to create this table
as a range partition on the end_time. However, when I try to add the pk I'm
getting an error :
ALTER TABLE object_revision ADD CONSTRAINT object_revision_id_end_time
PRIMARY KEY (id,end_time);
ERROR: column "end_time" contains null values
does someone familiar with a workaround ? I know that in postgresql as part
of the primary key definition unique and not null constraints are enforced
on each column and not on both of them. However, this might be problematic
with pg12 partitions..
