Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
Hi Michael, Thanks for the answer. I agree that the tables behind the views makes the query processing challenging. What makes it even more challenging to us is that this query is generated by a third party library that we use to operationalize the schema changes. I am trying to figure out what went wrong with query planning that hashjoins perform worse compared to index/sort joins. It looks to me that this is mostly because (1) the temporal space for creating a hashtable is a lot larger compared to sort/index joins and (2) it is *not *that the predicted selectivity is way off compared to the actual selectivity. W.r.t (1) in almost all cases the IOs needed to do hashing is way bigger compared to indexes (see in red if your email client supports html formatting, only in one parameter the hash joins "win" against the index/sort joins see in green, and the actual times are always worse, see in blue): -> Hash Join (cost=415.40..494.06 rows=263 width=136) (actual time=0.007..0.869 rows=1707 loops=1672) Output: c_5.conname, c_5.connamespace, r_5.relname, r_5.relnamespace Inner Unique: true Hash Cond: (c_5.conrelid = r_5.oid) Buffers: shared hit=87218 vs. corresponding index/sort join: -> Nested Loop (cost=0.28..171.05 rows=1 width=136) (actual time=0.024..1.976 rows=595 loops=2) Output: c_4.conname, c_4.connamespace, r_5.relname, r_5.relnamespace Inner Unique: true Buffers: shared hit=3674 or looking at the global level: Nested Loop (cost=2174.36..13670.47 rows=1 width=320) (actual time=5499.728..26310.137 rows=2 loops=1) Output: "*SELECT* 1".table_name, (a.attname)::information_schema.sql_identifier, "*SELECT* 1_1".table_name, (a_1.attname)::information_schema.sql_identifier, (con.conname)::information_schema.sql_identifier Inner Unique: true Buffers: shared hit=1961035 vs Nested Loop (cost=1736.10..18890.44 rows=1 width=320) (actual time=30.780..79.572 rows=2 loops=1) Output: "*SELECT* 1".table_name, (a.attname)::information_schema.sql_identifier, "*SELECT* 1_1".table_name, (a_1.attname)::information_schema.sql_identifier, (con.conname)::information_schema.sql_identifier Inner Unique: true Buffers: shared hit=9018 Which makes me wonder why hash join was chosen at all. Looks like a bug somewhere in query optimization. Cheers, Arturas On Fri, Sep 24, 2021 at 7:34 AM Michael Lewis wrote: > I believe that this is a planning problem with the number of tables/joins > involved in the query you have written. If you take a look at the > definition of the views in information_schema that you are using and read > about from_collapse_limit/join_collapse_limit, you may see that this is a > bit painful for the planner. It might be cumbersome to use the actual > system tables underneath, but that would certainly lead to much better > performance. Otherwise, I would look at perhaps putting the view that has a > WHERE condition on it as the FROM to encourage the planner to perhaps > filter that set first and join the other tables after. If that didn't help, > I might even use a materialized CTE to force the issue. > > Hopefully a real expert will chime in with a better explanation of the > challenges or preferred solution. >
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
On 9/25/21 9:33 PM, [email protected] wrote: >> > EDB has now published new installers for versions later than release >> > 11, containing Postgres built with an earlier version of gettext that >> > does not exhibit the problem. Please verify that these fix the issue. >> > If you already have Postgres installed from our installer you should >> > be able to upgrade using Stackbuilder. Otherwise, you can download >> > from our usual download sites. >> > >> > cheers >> > >> > andrew >> > >> > -- >> > Andrew Dunstan >> > EDB: https://www.enterprisedb.com > > > Hello Andrew, > > I just download the 13.4 Windows x86-64 installer from > https://www.enterprisedb.com/downloads/postgres-postgresql-downloads but it's > the exact same file bit for bit from the previous version I had. Am I looking > at the wrong place? > Thanks. We're dealing with that. However, you can update that version via stackbuilder. It will show you that 13.4.2 is available. This has the correct libintl DLL. I just did this to verify it. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
I'm unclear what you changed to get the planner to choose one vs the other. Did you disable hashjoins? Without the full plan to review, it is tough to agre with any conclusion that these particular nodes are troublesome. It might be that this was the right choice for that part of that plan, but improper estimates at a earlier step were problematic. What configs have you changed such as work_mem, random_page_cost, and such? If random_page_cost & seq_page_cost are still default values, then the planner will tend to do more seq scans I believe, and hash them to join with large sets of data, rather than do nested loop index scans. I think that's how that works. With the lack of flexibility to change the query, you might be able to set a few configs for the user that runs these schema checks. If you can find changes that make an overall improvement. *Michael Lewis | Database Engineer* *Entrata* >
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
On 9/27/21 6:55 PM, Andrew Dunstan wrote: Hello Andrew, I just download the 13.4 Windows x86-64 installer fromhttps://www.enterprisedb.com/downloads/postgres-postgresql-downloads but it's the exact same file bit for bit from the previous version I had. Am I looking at the wrong place? Thanks. We're dealing with that. However, you can update that version via stackbuilder. It will show you that 13.4.2 is available. This has the correct libintl DLL. I just did this to verify it. Thanks, look like the issue is fixed now, you can try to download the 'postgresql-13.4-2-windows-x64.exe' installer from the above mentioned link. -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company
RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
From: tushar Sent: Monday, September 27, 2021 11:50 To: Andrew Dunstan ; [email protected]; Julien Rouhaud Cc: Tom Lane ; Ranier Vilela ; Justin Pryzby ; [email protected] Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 On 9/27/21 6:55 PM, Andrew Dunstan wrote: Hello Andrew, I just download the 13.4 Windows x86-64 installer from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads but it's the exact same file bit for bit from the previous version I had. Am I looking at the wrong place? Thanks. We're dealing with that. However, you can update that version via stackbuilder. It will show you that 13.4.2 is available. This has the correct libintl DLL. I just did this to verify it. Thanks, look like the issue is fixed now, you can try to download the 'postgresql-13.4-2-windows-x64.exe' installer from the above mentioned link. -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company Fantastic, I may be able to try again tonight and will report back. The environment I work in is isolated from the internet, so I can't use StackBuilder. Thank you, Laurent.
Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
I Hi Michael, Thanks a lot for having a look at the query once again in more detail. In short, you are right, I fired the liquibase scripts and observed the exact query that was hanging in pg_stats_activity. The query was: SELECT FK.TABLE_NAME as "TABLE_NAME" , CU.COLUMN_NAMEas "COLUMN_NAME" , PK.TABLE_NAME as "REFERENCED_TABLE_NAME" , PT.COLUMN_NAMEas "REFERENCED_COLUMN_NAME" , C.CONSTRAINT_NAME as "CONSTRAINT_NAME" FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME , i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE lower(FK.TABLE_NAME)='secrole_condcollection' I rerun this query twice. Once with set enable_hashjoin = false; and set enable_hashjoin = true; . I observed that the join order was very, very similar between the hash and index plans. I reran the above two queries with random_page_cost to 2, 1.5, or 1.0 and observed no difference whatsoever, the planner was always choosing the hashjoins over sort/index nested loops. the seq_page_cost is set to default value 1. The tables behind the views do not have more than 10K rows, and do not exceed 400KB of space. The work_mem parameter is set to 256MB, effective cache is 9GB, the machine has something around 32-64GB of RAM, SSD as the primary drive, 140 default connections. The query planner, of course thinks that the overall nested loop including hashes is better: cost=2174.36..13670.47 (hash) vs cost=1736.10..18890.44 (index/sort join) but I think there's a problem there, cause I don't think that one can reuse the pre-computed hashes over and over again, while sort/index joins end up hitting the same buffers, or am I wrong? More details about the query plans as well as the complete set of settings can be found in the original email at https://www.postgresql.org/message-id/CAAUL%3DcFcvUo%3D7b4T-K5PqiqrF6etp59qcgv77DyK2Swa4VhYuQ%40mail.gmail.com If you could have another look into what's going on, I'd appreciate it a lot. in postgres 9.6 our setup goes through the liquibase scripts in 5 minutes, and pg12 with hash joins may take up to 1.5 hours. Cheers, Arturas On Mon, Sep 27, 2021 at 4:12 PM Michael Lewis wrote: > I'm unclear what you changed to get the planner to choose one vs the > other. Did you disable hashjoins? Without the full plan to review, it is > tough to agre with any conclusion that these particular nodes are > troublesome. It might be that this was the right choice for that part of > that plan, but improper estimates at a earlier step were problematic. > > What configs have you changed such as work_mem, random_page_cost, and > such? If random_page_cost & seq_page_cost are still default values, > then the planner will tend to do more seq scans I believe, and hash them to > join with large sets of data, rather than do nested loop index scans. I > think that's how that works. With the lack of flexibility to change the > query, you might be able to set a few configs for the user that runs these > schema checks. If you can find changes that make an overall improvement. > > > *Michael Lewis | Database Engineer* > *Entrata* > >>
Partial index on enum type is not being used, type issue?
I have run into the following issue: A table contains an enum column,
and a partial unique index is available on the table.
This index contains exactly the row I am querying for. Unfortunately the
index is not always used, and I don't really understand why.
The attachments enumTest.sql shows the script reproducing the behaviour,
and the enumTest.log shows the result when running on PostgreSQL 13.4.
There doesn't seem to be any difference from PG11 through 14-RC1.
First off I tried to do a simple test to see if the index was being used:
EXPLAIN (analyze, costs, buffers, verbose) SELECT val FROM
table_test_enum WHERE val = 'Ole' and dat IS NULL;
QUERY PLAN
Index Only Scan using table_test_enum_val_idx on
public.table_test_enum (cost=0.12..4.14 rows=1 width=4) (actual
time=0.014..0.016 rows=1 loops=1)
Output: val
Heap Fetches: 0
Planning Time: 0.436 ms
Execution Time: 0.048 ms
(5 rows)
All is fine, but in my application the query is executed as a prepared
statement, using a varchar parameter:
PREPARE qry1(varchar) AS SELECT val FROM table_test_enum WHERE val =
$1::type_table_test_enum AND dat IS NULL;
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry1('Ole');
QUERY PLAN
--
Seq Scan on public.table_test_enum (cost=0.00..66.52 rows=1 width=4)
(actual time=1.131..1.133 rows=1 loops=1)
Output: val
Filter: ((table_test_enum.dat IS NULL) AND (table_test_enum.val =
('Ole'::cstring)::type_table_test_enum))
Rows Removed by Filter: 3000
Planning Time: 0.261 ms
Execution Time: 1.162 ms
(6 rows)
To my surprise the planner decides not to use the index. This is the
part I do not understand. Why is the result different here?
There is obviously a cast that happens before the equality, does the
cstring cast have anything to do with this? Hints are welcome!
So I tried to prepare a statement with a parameter of type
type_table_test_enum instead, unsurprisingly, this works fine. No
mentioning of cstring in the plan.
I also tried to use a parameter of unknown type, which I would think
would be analogous to the first statement with the literal 'Ole', and
that looks fine.
So why is the varchar version not using the index?
Any thoughs on this?
Regards,
Kim Johan Andersson
SELECT version();
CREATE TYPE type_table_test_enum AS ENUM ( 'Ole', 'Bole', 'Skole' );
CREATE TABLE table_test_enum ( val type_table_test_enum NOT NULL, dat date );
CREATE UNIQUE INDEX ON table_test_enum ( val ) WHERE val = 'Ole' AND dat IS
NULL;
INSERT INTO table_test_enum SELECT 'Ole', current_date FROM generate_series( 1,
1000 );
INSERT INTO table_test_enum SELECT 'Bole', current_date FROM generate_series(
1, 1000 );
INSERT INTO table_test_enum SELECT 'Skole', current_date FROM generate_series(
1, 1000 );
INSERT INTO table_test_enum VALUES ( 'Ole', NULL );
VACUUM (analyze) table_test_enum;
EXPLAIN (analyze, costs, buffers, verbose) SELECT val FROM table_test_enum
WHERE val = 'Ole' and dat IS NULL;
PREPARE qry1(varchar) AS SELECT val FROM table_test_enum WHERE val =
$1::type_table_test_enum AND dat IS NULL;
PREPARE qry2(type_table_test_enum) AS select val FROM table_test_enum WHERE val
= $1 AND dat IS NULL;
PREPARE qry3(unknown) AS SELECT val FROM table_test_enum WHERE val = $1 AND dat
IS NULL;
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry1('Ole');
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry2('Ole');
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry3('Ole');
DEALLOCATE qry1;
DEALLOCATE qry2;
DEALLOCATE qry3;
DROP TABLE table_test_enum;
DROP TYPE type_table_test_enum; version
--
PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by
gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
CREATE TYPE
CREATE TABLE
CREATE INDEX
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
INSERT 0 1
VACUUM
QUERY PLAN
-
Index Only Scan using table_test_enum_val_idx on public.table_test_enum
(cost=0.12..4.14 rows=1 width=4) (actual time=0.014..0.016 rows=1 loops=1)
Output: val
Heap Fetches: 0
Planning Time: 0.436 ms
Execution Time: 0.048 ms
(5 rows)
PREPARE
PREPARE
PREPARE
QUERY PLAN
-
Seq Scan on public.table_test_enum (cost=0.00..66.52 rows=1 width=4) (actual
tim
Re: Partial index on enum type is not being used, type issue?
Kim Johan Andersson writes: > [ uses partial index: ] > EXPLAIN (analyze, costs, buffers, verbose) SELECT val FROM > table_test_enum WHERE val = 'Ole' and dat IS NULL; > > [ doesn't: ] > PREPARE qry1(varchar) AS SELECT val FROM table_test_enum WHERE val = > $1::type_table_test_enum AND dat IS NULL; There's no actual cast from varchar to that enum type. The system is letting you get away with it anyway, by applying what's called a CoerceViaIO cast --- which means convert the varchar to a simple string (cstring) and then apply enum_in(). Unfortunately for you, enum_in() is marked stable not immutable (probably on the grounds that it depends on catalog contents) so the expression isn't reduced to a plain constant during constant-folding and thus fails to match the partial index's WHERE clause. In the first case, 'Ole' is taken as a constant of type type_table_test_enum right off the bat, as was the same constant in the index's WHERE clause, so everything matches fine. (This seems a little inconsistent now that I think about it --- if it's okay to fold the literal to an enum constant at parse time, why can't we do the equivalent at plan time? But these rules have stood for a good while so I'm hesitant to change them.) Anyway, the recommendable solution is the one you already found: declare the PREPARE's argument as type_table_test_enum not varchar. regards, tom lane
RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
From: tushar Sent: Monday, September 27, 2021 11:50 To: Andrew Dunstan ; [email protected]; Julien Rouhaud Cc: Tom Lane ; Ranier Vilela ; Justin Pryzby ; [email protected] Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 On 9/27/21 6:55 PM, Andrew Dunstan wrote: Hello Andrew, I just download the 13.4 Windows x86-64 installer from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads but it's the exact same file bit for bit from the previous version I had. Am I looking at the wrong place? Thanks. We're dealing with that. However, you can update that version via stackbuilder. It will show you that 13.4.2 is available. This has the correct libintl DLL. I just did this to verify it. Thanks, look like the issue is fixed now, you can try to download the 'postgresql-13.4-2-windows-x64.exe' installer from the above mentioned link. -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company --- Hello all! WOW Time for a cigar as there is double good news 😊 - The scenario no longer exacerbates the system and performance went from around 90s to around 2.7 seconds! That's in line with older 11.2 builds I was measuring against. - The simpler scenario (no throw) looks like it improved by roughly 20%, from 186ms to 146ms I had run the scenarios multiple times before and the times were on the average, so I think those gains are real. Thank you for all your efforts. The Postgres community is amazing! Here is the scenario again: drop table sampletest; create table sampletest (a varchar, b varchar); insert into sampletest (a, b) select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar from generate_series(1,10); CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) RETURNS real AS $$ BEGIN RETURN case when str is null then val else str::real end; EXCEPTION WHEN OTHERS THEN RETURN val; END; $$ LANGUAGE plpgsql COST 1 IMMUTABLE; This is what I had on the original 13.4 Windows x64 eDB build: explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as "a" from sampletest --Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual time=89527.032..89527.033 rows=1 loops=1) -- Buffers: shared hit=647 -- -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.024..37.811 rows=10 loops=1) --Buffers: shared hit=637 --Planning: -- Buffers: shared hit=24 --Planning Time: 0.347 ms --Execution Time: 89527.501 ms explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(b, null)) as "b" from sampletest --Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=186.605..186.606 rows=1 loops=1) -- Buffers: shared hit=637 -- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=8) (actual time=0.008..9.679 rows=10 loops=1) --Buffers: shared hit=637 --Planning: -- Buffers: shared hit=4 --Planning Time: 0.339 ms --Execution Time: 186.641 ms This is what I get on the new build explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as "a" from sampletest --QUERY PLAN | -| --Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=2711.314..2711.315 rows=1 loops=1) | -- Buffers: shared hit=637 | -- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=15) (actual time=0.009..12.557 rows=10 loops=1)| --Buffers: shared hit=637 | --Planning Time: 0.062 ms | --Execution Time: 2711.336 ms | explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(b, null)) as "b" from sampletest --QUERY PLAN | ---| --Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=146.689..146.689 rows=1 loops=1) | -- Buffers: shared hit=637 | -- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=8) (actual time=0.009..8.060 rows=10 loops=1)| --Buffers: shared hit=637
