[SQL] Quick select, slow update - help with performance problems
Hi folks. My system is slowing down, more notably over the last few weeks. The main reason is that it's on an old slow machine, and I'm in the process of sorting this. However, I think that there are some issues within my database which I need to investigate. There seems to be some performance issues with updating the database. I have a select, an insert and an update based on the same table and condition. The select: select count(ud_id) from used_diary where ud_valet_completed is null and ud_valet_required < CURRENT_DATE-'7 days'::interval; completed in about a second. The insert insert into used_diary_log (ul_u_id, ul_ud_id, ul_changes) select 25, ud_id, 'Valet automatically cleared down' from used_diary where ud_valet_completed is null and ud_valet_required < CURRENT_DATE-'7 days'::interval; also completed in about a second. However the update update used_diary set ud_valet_completed=now(), ud_valet_completed_by=25 where ud_valet_completed is null and ud_valet_required < CURRENT_DATE-'7 days'::interval is still running after approx 1 1/2 minutes. I've noticed that other updates also seem to take a long time. Could I have any suggestions on how I could start looking into why this is. Could it be the config of postgresql, issues with my schema, or something else? Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Quick select, slow update - help with performance problems
Gary Stainburn wrote: update used_diary set ud_valet_completed=now(), ud_valet_completed_by=25 where ud_valet_completed is null and ud_valet_required < CURRENT_DATE-'7 days'::interval is still running after approx 1 1/2 minutes. I've noticed that other updates also seem to take a long time. Do you have any foreign keys referencing used_diary? Do they have the correct indexes on the referencing tables? Any on-update triggers? -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Quick select, slow update - help with performance problems
On Tuesday 01 July 2008 12:17, Richard Huxton wrote:
> Gary Stainburn wrote:
> > update used_diary set
> > ud_valet_completed=now(), ud_valet_completed_by=25
> > where ud_valet_completed is null and
> > ud_valet_required < CURRENT_DATE-'7 days'::interval
> >
> > is still running after approx 1 1/2 minutes. I've noticed that other
> > updates also seem to take a long time.
>
> Do you have any foreign keys referencing used_diary? Do they have the
> correct indexes on the referencing tables?
> Any on-update triggers?
>
After about 5 minutes I Ctrl+C'd and then re-sent the update and it completed
in about 5 seconds, so I don't know what happened there.
Below is the \d for the table. I can't see how updating those two fields would
cause a problem though.
I've also included the explain, which looks very straight-forward
goole=# \d used_diary
Table "public.used_diary"
Column |Type |
Modifiers
---+-+-
-
ud_id | integer | not null default
nex
tval(('"used_diary_ud_id_seq"'::text)::regclass)
ud_d_id | integer | not null
ud_registration | character varying(12) |
ud_stock | character varying(7)|
ud_name | character varying(50) |
ud_required | date|
ud_rfl| character varying(25) |
ud_comments | text|
ud_created| timestamp with time zone| default now()
ud_completed | timestamp with time zone|
ud_u_id | integer | not null
ud_completed_by | integer |
ud_dd_id | integer | not null default 6
ud_authorized | timestamp without time zone |
ud_authorized_by | integer |
ud_tab| integer |
ud_tos_id | integer |
ud_debt | numeric(7,2)|
ud_m_id | integer |
ud_cc_id | character(2)|
ud_required_time | character varying(5)|
ud_tr_id | integer |
ud_pex_exists | boolean |
ud_pex_registration | character varying(12) |
ud_pex_make_model | character varying(40) |
ud_valet_instructions | text|
ud_valet_completed| timestamp without time zone |
ud_valet_completed_by | integer |
ud_pex_valet_completed| timestamp without time zone |
ud_pex_valet_completed_by | integer |
ud_pex_valet_option | integer |
ud_pex_valet_instructions | text|
ud_do_valet | boolean | default true
ud_valet_required | date|
ud_handover_date | date|
ud_phone_no | character varying(20) |
ud_valet_site | integer |
ud_ps_id | integer |
ud_partex_prep| text|
Indexes:
"used_diary_pkey" PRIMARY KEY, btree (ud_id)
"used_diary_completed_index" btree (ud_completed)
"used_diary_dealer_index" btree (ud_d_id)
"used_diary_dept_index" btree (ud_dd_id)
"used_diary_handover_date" btree (ud_handover_date)
"used_diary_ps_id" btree (ud_ps_id)
"used_diary_reg_index" btree (ud_registration)
"used_diary_required" btree (ud_required)
"used_diary_stock_index" btree (ud_stock)
"used_diary_ud_pex_valet_completed" btree (ud_pex_valet_completed)
"used_diary_ud_valet_completed" btree (ud_valet_completed)
"used_diary_valet_required" btree (ud_valet_required)
Foreign-key constraints:
"used_diary_ud_authorized_by_fkey" FOREIGN KEY (ud_authorized_by)
REFERENCE
S users(u_id)
"used_diary_ud_cc_id_fkey" FOREIGN KEY (ud_cc_id) REFERENCES
contract_codes
(cc_id)
"used_diary_ud_dd_id_fkey" FOREIGN KEY (ud_dd_id) REFERENCES
diary_departme
nts(dd_id)
"used_diary_ud_m_id_fkey" FOREIGN KEY (ud_m_id) REFERENCES
stock_makes(m_id
)
"used_diary_ud_pex_valet_completed_by_fkey" FOREIGN KEY
(ud_pex_valet_compl
eted_by) REFERENCES users(u_id)
"used_diary_ud_pex_valet_option_fkey" FOREIGN KEY (ud_pex_valet_option)
REF
ERENCES diary_valet_options(dv_id)
"used_diary_ud_ps_id_fkey" FOREIGN KEY (ud_ps_id) REFERENCES
partex_state(p
s_id)
"used_diary_ud_tab_fkey" FOREIGN KEY (ud_tab) REFERENCES tax_tabs
[SQL] Need a sample Postgre SQL script
Hi All, I'm kind of new to Postgre and I need some advice. I have the following table. metadata (value:integer , field:integer , mydate:text) given below is a sample record from that. ( 2 , 16 , Augest 2009) I need a script that will read the above table and for each such row it will insert two rows as below. ( 2 , 91 , Augest ) ( 2 , 86 , 2009 ) 16, 91 and 86 are static values. *value and field together* creates the primary key. I could figure out and write the logic for this. I only need a little bit similar sample script so that I can figure out Postgre syntax. Like - Loop syntax to through all the rows return by a select * - How to assign that value to a parameter - using that parameter in the Insert statement - etc ... Thanks, Dhanushka.
Re: [SQL] Need a sample Postgre SQL script
Dhanushka Samarakoon wrote: Hi All, I'm kind of new to Postgre and I need some advice. No problem. It's PostgreSQL or Postgres by the way. I have the following table. metadata (value:integer , field:integer , mydate:text) given below is a sample record from that. ( 2 , 16 , Augest 2009) I need a script that will read the above table and for each such row it will insert two rows as below. ( 2 , 91 , Augest ) ( 2 , 86 , 2009 ) 16, 91 and 86 are static values. *value and field together* creates the primary key. CREATE TEMP TABLE staticfields (f integer); INSERT INTO staticfields VALUES (91); INSERT INTO staticfields VALUES (86); INSERT INTO metadata (value, field, mydate) SELECT value, f, mydate FROM metadata, staticfields; -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need a sample Postgre SQL script
Thanks for the reply. But one problem I have is I need to loop through all the rows in the table and in each iteration I need to fetch the value of mydate in to a variable and split it to month and year and add two rows with *value, 91, month* (2 , 91, Augest) and *value, 86, year* (2 , 86 , 2009) So I need an idea on - how to loop through all the rows thats returned by a select statement. - for each statement how to get the value of mydate in to a variable, so that I can use SubString to split it in to date and year and use them in the insert statement. On Tue, Jul 1, 2008 at 9:42 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: > Dhanushka Samarakoon wrote: > >> Hi All, >> >> I'm kind of new to Postgre and I need some advice. >> > > No problem. It's PostgreSQL or Postgres by the way. > > I have the following table. >> metadata (value:integer , field:integer , mydate:text) >> >> given below is a sample record from that. >> ( 2 , 16 , Augest 2009) >> >> I need a script that will read the above table and for each such row it >> will >> insert two rows as below. >> >> ( 2 , 91 , Augest ) >> ( 2 , 86 , 2009 ) >> >> 16, 91 and 86 are static values. *value and field together* creates the >> primary key. >> > > CREATE TEMP TABLE staticfields (f integer); > INSERT INTO staticfields VALUES (91); > INSERT INTO staticfields VALUES (86); > > INSERT INTO metadata (value, field, mydate) > SELECT value, f, mydate > FROM metadata, staticfields; > > -- > Richard Huxton > Archonet Ltd >
Re: [SQL] Need a sample Postgre SQL script
Dhanushka Samarakoon wrote: Thanks for the reply. But one problem I have is I need to loop through all the rows in the table and in each iteration I need to fetch the value of mydate in to a variable and split it to month and year and add two rows with *value, 91, month* (2 , 91, Augest) and *value, 86, year* (2 , 86 , 2009) So I need an idea on - how to loop through all the rows thats returned by a select statement. - for each statement how to get the value of mydate in to a variable, so that I can use SubString to split it in to date and year and use them in the insert statement. Ah, looking back I see "mydate" isn't actually a date. Note the space in the pattern for substring() below: INSERT INTO metadata (value, field, mydate) SELECT value,91, substring(mydate, '(.+) ') FROM metadata UNION ALL SELECT value, 86, substring(mydate, ' (.+)') FROM metadata; Does that do it for you? Try the SELECT clauses by themselves to check if they're doing the right thing. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] column default dependant on another columns value
Hi list, Given a table with columns seconds and minutes, how can I have minutes be computed automatically at the insert statement? I tried: ALTER TABLE table1 ALTER COLUMN minutes SET default (seconds/60); Postgres' answer was: ERROR: cannot use column references in default expression So I gave rules a look but it seems rules apply to the entire row. CREATE RULE "my_rule" AS ON INSERT TO table1 WHERE minutes is null DO INSTEAD INSERT INTO table1 (column1, column2, seconds, minutes) VALUES(new.column1, new.column2, new.seconds, new.seconds/60); Is this correct? Is there another (better/simpler) way to achieve this? Regards, Fernando -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column default dependant on another columns value
On Tue, Jul 1, 2008 at 1:12 PM, Fernando Hevia <[EMAIL PROTECTED]> wrote: > Given a table with columns seconds and minutes, how can I have minutes be > computed automatically at the insert statement? It is possible to do this with a trigger or a rule. A trigger would be more robust. > Is this correct? Is there another (better/simpler) way to achieve this? Well I might work, but it is a bad practice to get into since what you are trying to do violates the rules of database normalization. Wouldn't it be better to calculate the minutes with you query your table? SELECT *, seconds / 60 AS minutes FROM yourtable; -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column default dependant on another columns value
> -Mensaje original- > De: Richard Broersma [mailto:[EMAIL PROTECTED] > > It is possible to do this with a trigger or a rule. A > trigger would be more robust. > > > Is this correct? Is there another (better/simpler) way to > achieve this? > > Well I might work, but it is a bad practice to get into since > what you are trying to do violates the rules of database > normalization. > > Wouldn't it be better to calculate the minutes with you query > your table? > > SELECT *, seconds / 60 AS minutes > FROM yourtable; Actually I only used this as an example. The real table is queried lots of times for millions of rows and the server is showing some high-level user cpu consumption. There are a couple calculated columns on the table so I am trying to reduce cpu usage by pre-calculating the more cpu intensive data once on insert. Enhancing the application is currently not possible. Anyway, the rule didn't work. Got "an infinite recursion error" when inserting on the table. Can't figure out where the recursion is as supposedly the rule kicks in when the "where minutes is null" condition is satisfied. The DO INSTEAD part runs an insert were minutes is NOT null so the rule should be ignored. Where is the recursion then? I am on postgres 8.2.9. Thanks for your hindsight Richard. I Will look into the trigger solution. Still, I'd like to understand this recursion error. Regards, Fernando. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column default dependant on another columns value
"Fernando Hevia" <[EMAIL PROTECTED]> writes: > Anyway, the rule didn't work. Got "an infinite recursion error" when > inserting on the table. > Can't figure out where the recursion is You didn't show us the rule, but I imagine that you think the WHERE clause is applied while expanding the rule. It's not, it can only suppress rows at run-time; and what you've got is infinite macro expansion recursion. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
