Re: performance of analytical query
On Fri, Nov 12, 2021 at 09:12:38PM +0100, Jiří Fejfar wrote: > * I know that PG is focused on OLTP rather then analytics, but we are happy > with it at all and do not wish to use another engine for analytical > queries... isn't somewhere some "PG analytical best practice" available? It's a good question. Here's some ideas: I don't think we know what version you're using - that's important, and there's other ideas here: https://wiki.postgresql.org/wiki/Slow_Query_Questions You said that your query was slow "probably after VACUUM ANALYZE". Is it really faster without stats ? You can do this to see if there was really a better plan "before": | begin; DELETE FROM pg_statistic WHERE starelid='thetable'::regclass; explain analyze ...; rollback; Try enable_nestloop=off for analytic queries; Test whether jit=off helps you or hurts you (you said that it's already disabled); You can do other things that can improve estimates, by sacrificing planning time (which for an analytic query is a small component of the total query time, and pays off at runtime if you can get a btter plan): - FKs can help with estimates since pg9.6; - CREATE STATISTICS; - ALTER SET STATISTICS or increase default_statistics_target; - increase from_collapse_limit and join_collapse_limit. But I don't think it will help your current query plan. - partitioning data increases planning time, and (if done well) can allow improved execution plans; You can REINDEX or maybe CLUSTER during "off hours" to optimize indexes/tables. BRIN indexes (WITH autoanalyze) are very successful for us, here. You can monitor your slow queries using auto_explain and/or pg_stat_statements. You can reduce autovacuum_analyze_threshold to analyze more often. I'd be interested to hear if others have more suggestions. -- Justin
Out of memory error
Hi, In a trigger function I am creating a temp table . When an update on a table is executed for say 10k rows. I get the below error. ERROR: out of shared memory HINT:You might need to increase max_locks_per_transaction CONTEXT: SQL Statement "created temp table changedinfo(colName varchar(100), oldValue varchar(4000), newValue varchar(4000) Current value of max_locks_per_transaction is 64. Do I have to increase this? Regards, Aditya.
Re: Out of memory error
aditya desai writes: > In a trigger function I am creating a temp table . When an update on a > table is executed for say 10k rows. I get the below error. > ERROR: out of shared memory > HINT:You might need to increase max_locks_per_transaction > CONTEXT: SQL Statement "created temp table changedinfo(colName > varchar(100), oldValue varchar(4000), newValue varchar(4000) [ raised eyebrow ... ] If you are concerned about performance, I'd start by not creating a temp table per row of the outer update. That's costing probably 100x to 1000x as much as the row update itself. regards, tom lane
Re: Out of memory error
Thanks Tom. However I could not find any solution to achieve the given
requirement. I have to take all values in the temp table and assign it to
an array variable to pass it to the audit procedure as shown below. Can you
please advise ?
CREATE OR REPLACE FUNCTION call_insert_info(
) RETURNS void AS $$
DECLARE
v_message r_log_message[];
OLDVALUE1 varchar(4000);
BEGIN
drop table if exists changedinfo
create temp table changedinfo(colName varchar(100), oldValue
varchar(4000), newValue varchar(4000));
insert into changed infot select 'empName', OLD.empName,
NEW.empName from employee;
insert into changed infot select 'location', OLD.location,
NEW.location from employee;
v_message:= array(select '(' || columname || ',' || oldvalue || ',' ||
newvalue ||')' from changedinfo);
perform insert_info(v_message);
raise notice '%',v_message;
END;
$$ LANGUAGE plpgsql;
Regards,
AD.
On Wed, Nov 24, 2021 at 11:22 AM Tom Lane wrote:
> aditya desai writes:
> > In a trigger function I am creating a temp table . When an update on a
> > table is executed for say 10k rows. I get the below error.
>
> > ERROR: out of shared memory
> > HINT:You might need to increase max_locks_per_transaction
> > CONTEXT: SQL Statement "created temp table changedinfo(colName
> > varchar(100), oldValue varchar(4000), newValue varchar(4000)
>
> [ raised eyebrow ... ] If you are concerned about performance,
> I'd start by not creating a temp table per row of the outer update.
> That's costing probably 100x to 1000x as much as the row update itself.
>
> regards, tom lane
>
Re: Out of memory error
aditya desai schrieb am 24.11.2021 um 07:25:
> Thanks Tom. However I could not find any solution to achieve the given
> requirement. I have to take all values in the temp table and assign it to an
> array variable to pass it to the audit procedure as shown below. Can you
> please advise ?
>
> CREATE OR REPLACE FUNCTION call_insert_info(
>
> ) RETURNS void AS $$
> DECLARE
> v_message r_log_message[];
> OLDVALUE1 varchar(4000);
> BEGIN
> drop table if exists changedinfo
> create temp table changedinfo(colName varchar(100), oldValue
> varchar(4000), newValue varchar(4000));
> insert into changed infot select 'empName', OLD.empName,
> NEW.empName from employee;
> insert into changed infot select 'location', OLD.location,
> NEW.location from employee;
>
>
> v_message:= array(select '(' || columname || ',' || oldvalue || ',' ||
> newvalue ||')' from changedinfo);
> perform insert_info(v_message);
> raise notice '%',v_message;
> END;
> $$ LANGUAGE plpgsql;
You don't need a temp table for that. You can create the array directly from
the new and old records:
v_message := array[concat_ws(',', 'empName', old.empname, new.empname),
concat_ws(',', 'location', old.location, new.location)];
Although nowadays I would probably pass such an "structure" as JSON though, not
as a comma separated list.
Re: Out of memory error
Ok. Let me try this. Thanks!!
On Wed, Nov 24, 2021 at 12:01 PM Thomas Kellerer wrote:
> aditya desai schrieb am 24.11.2021 um 07:25:
> > Thanks Tom. However I could not find any solution to achieve the given
> requirement. I have to take all values in the temp table and assign it to
> an array variable to pass it to the audit procedure as shown below. Can you
> please advise ?
> >
> > CREATE OR REPLACE FUNCTION call_insert_info(
> >
> > ) RETURNS void AS $$
> > DECLARE
> > v_message r_log_message[];
> > OLDVALUE1 varchar(4000);
> > BEGIN
> > drop table if exists changedinfo
> > create temp table changedinfo(colName varchar(100), oldValue
> varchar(4000), newValue varchar(4000));
> > insert into changed infot select 'empName', OLD.empName,
> NEW.empName from employee;
> > insert into changed infot select 'location', OLD.location,
> NEW.location from employee;
> >
> >
> > v_message:= array(select '(' || columname || ',' || oldvalue || ',' ||
> newvalue ||')' from changedinfo);
> > perform insert_info(v_message);
> > raise notice '%',v_message;
> > END;
> > $$ LANGUAGE plpgsql;
>
>
> You don't need a temp table for that. You can create the array directly
> from the new and old records:
>
> v_message := array[concat_ws(',', 'empName', old.empname,
> new.empname), concat_ws(',', 'location', old.location, new.location)];
>
> Although nowadays I would probably pass such an "structure" as JSON
> though, not as a comma separated list.
>
>
>
>
Re: Out of memory error
It seems like that function has some syntax errors, and also doesn't do what you want since I presume the "from employee" bit would mean you get many rows inserted into that temp table for all the existing data and not the one row you are operating on at the moment the trigger fires. It is worth noting also that if bulk operations are at all common for this table then writing this as an after statement trigger will likely be helpful for performance. For full context, we'd need to see how the function insert_info is defined.
Re: Out of memory error
H Michael, Please see insert_info function below. Also r_log_message is composite data type and it's definition is also given below. CREATE OR REPLACE FUNCTION insert_info( info_array r_log_message[] ) RETURNS varchar AS $$ DECLARE info_element r_log_message; BEGIN FOREACH info_element IN ARRAY info_array LOOP INSERT INTO testaditya( columname, oldvalue, newvalue ) VALUES( info_element.column_name, info_element.oldvalue, info_element.newvalue ); END LOOP; RETURN 'OK'; END; $$ LANGUAGE plpgsql; postgres=# \d r_log_message; Composite type "public.r_log_message" Column| Type | Collation | Nullable | Default -+-+---+--+- column_name | character varying(30) | | | oldvalue| character varying(4000) | | | newvalue| character varying(4000) | | | Regards, Aditya. On Wed, Nov 24, 2021 at 12:16 PM Michael Lewis wrote: > It seems like that function has some syntax errors, and also doesn't do > what you want since I presume the "from employee" bit would mean you get > many rows inserted into that temp table for all the existing data and not > the one row you are operating on at the moment the trigger fires. > > It is worth noting also that if bulk operations are at all common for this > table then writing this as an after statement trigger will likely be > helpful for performance. > > For full context, we'd need to see how the function insert_info is defined. >
Re: Out of memory error
Hi Thomas,
v_message is of composite data type r_log_message and it's definition is as
shown below.
postgres=# \d r_log_message;
Composite type "public.r_log_message"
Column| Type | Collation | Nullable | Default
-+-+---+--+-
column_name | character varying(30) | | |
oldvalue| character varying(4000) | | |
newvalue| character varying(4000) | | |
Regards,
Aditya.
On Wed, Nov 24, 2021 at 12:01 PM Thomas Kellerer wrote:
> aditya desai schrieb am 24.11.2021 um 07:25:
> > Thanks Tom. However I could not find any solution to achieve the given
> requirement. I have to take all values in the temp table and assign it to
> an array variable to pass it to the audit procedure as shown below. Can you
> please advise ?
> >
> > CREATE OR REPLACE FUNCTION call_insert_info(
> >
> > ) RETURNS void AS $$
> > DECLARE
> > v_message r_log_message[];
> > OLDVALUE1 varchar(4000);
> > BEGIN
> > drop table if exists changedinfo
> > create temp table changedinfo(colName varchar(100), oldValue
> varchar(4000), newValue varchar(4000));
> > insert into changed infot select 'empName', OLD.empName,
> NEW.empName from employee;
> > insert into changed infot select 'location', OLD.location,
> NEW.location from employee;
> >
> >
> > v_message:= array(select '(' || columname || ',' || oldvalue || ',' ||
> newvalue ||')' from changedinfo);
> > perform insert_info(v_message);
> > raise notice '%',v_message;
> > END;
> > $$ LANGUAGE plpgsql;
>
>
> You don't need a temp table for that. You can create the array directly
> from the new and old records:
>
> v_message := array[concat_ws(',', 'empName', old.empname,
> new.empname), concat_ws(',', 'location', old.location, new.location)];
>
> Although nowadays I would probably pass such an "structure" as JSON
> though, not as a comma separated list.
>
>
>
>
