pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow update SQL
8+ messages / 5 participants
[nested] [flat]
* Slow update SQL
@ 2006-02-14 01:48 Ken Hill <ken@scottshill.com>
0 siblings, 2 replies; 8+ messages in thread
From: Ken Hill @ 2006-02-14 01:48 UTC (permalink / raw)
To: pgsql-sql
I'm experiencing a very slow query. The table contains 611,564 rows of
data. I vaccumed the table:
VACUUM ANALYZE ncccr10;
SELECT count(*) FROM ncccr10;
count
--------
611564
(1 row)
When I try to analyze the query plan with:
EXPLAIN ANALYZE
UPDATE ncccr10
SET key = facilityno||'-'||
lastname||'-'||
sex||'-'||
ssno||'-'||
birthdate||'-'||
primarysit||'-'||
dxdate||'-'||
morphology3
WHERE date_part('year',dxdate) > '2000';
The query just never finishes (even 1 hour later). The colum key100 is
indexed, and I'm setting the value of this
column from other columns. Why is this so slow?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Slow update SQL
@ 2006-02-14 02:14 Michael Fuhr <mike@fuhr.org>
parent: Ken Hill <ken@scottshill.com>
1 sibling, 1 reply; 8+ messages in thread
From: Michael Fuhr @ 2006-02-14 02:14 UTC (permalink / raw)
To: Ken Hill <ken@scottshill.com>; +Cc: pgsql-sql
On Mon, Feb 13, 2006 at 05:48:45PM -0800, Ken Hill wrote:
> When I try to analyze the query plan with:
>
> EXPLAIN ANALYZE
> UPDATE ncccr10
> SET key = facilityno||'-'||
> lastname||'-'||
> sex||'-'||
> ssno||'-'||
> birthdate||'-'||
> primarysit||'-'||
> dxdate||'-'||
> morphology3
> WHERE date_part('year',dxdate) > '2000';
>
> The query just never finishes (even 1 hour later). The colum key100 is
> indexed, and I'm setting the value of this
> column from other columns. Why is this so slow?
If EXPLAIN ANALYZE is taking too long then could we at least see
the EXPLAIN output? How many rows does the condition match?
SELECT count(*) FROM ncccr10 WHERE date_part('year',dxdate) > '2000';
Do you have an expression index on date_part('year',dxdate)? Does
the table have any triggers or rules? Have you queried pg_locks
to see if the update is blocked on an ungranted lock?
Do other tables have foreign key references to ncccr10? If so then
you might need indexes on the referring columns.
What version of PostgreSQL are you running?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Slow update SQL
@ 2006-02-14 05:17 Michael Fuhr <mike@fuhr.org>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Fuhr @ 2006-02-14 05:17 UTC (permalink / raw)
To: Ken Hill <ken@scottshill.com>; +Cc: pgsql-sql
[Please copy the mailing list on replies.]
On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > How many rows does the condition match?
>
> csalgorithm=# SELECT count(*) FROM ncccr10 WHERE
> date_part('year',dxdate) > '2000';
> count
> --------
> 199209
> (1 row)
You're updating about a third of the table; an expression index on
date_part probably wouldn't help because the planner is likely to
stick with a sequential scan for such a large update. Even if it
did help it's likely to be a small fraction of the total time.
The table definition you sent me showed nine indexes. You might
see a substantial performance improvement by dropping all the
indexes, doing the update, then creating the indexes again (don't
forget to vacuum and analyze the table after the update). However,
dropping the indexes has obvious implications for other queries so
you might need to do the update at a time when that doesn't matter.
> > Have you queried pg_locks
> > to see if the update is blocked on an ungranted lock?
>
> I don't know what that is. How do I query pg_locks?
SELECT * FROM pg_locks;
http://www.postgresql.org/docs/7.4/static/monitoring-locks.html
> > What version of PostgreSQL are you running?
>
> 7.4.8. Thank you for your help.
Newer versions generally perform better; consider upgrading to 8.0
or 8.1 if possible.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Slow update SQL
@ 2006-02-14 16:35 Ken Hill <ken@scottshill.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 8+ messages in thread
From: Ken Hill @ 2006-02-14 16:35 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
On Mon, 2006-02-13 at 22:17 -0700, Michael Fuhr wrote:
> [Please copy the mailing list on replies.]
>
> On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> > On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > > How many rows does the condition match?
> >
> > csalgorithm=# SELECT count(*) FROM ncccr10 WHERE
> > date_part('year',dxdate) > '2000';
> > count
> > --------
> > 199209
> > (1 row)
>
> You're updating about a third of the table; an expression index on
> date_part probably wouldn't help because the planner is likely to
> stick with a sequential scan for such a large update. Even if it
> did help it's likely to be a small fraction of the total time.
>
> The table definition you sent me showed nine indexes. You might
> see a substantial performance improvement by dropping all the
> indexes, doing the update, then creating the indexes again (don't
> forget to vacuum and analyze the table after the update). However,
> dropping the indexes has obvious implications for other queries so
> you might need to do the update at a time when that doesn't matter.
>
> > > Have you queried pg_locks
> > > to see if the update is blocked on an ungranted lock?
> >
> > I don't know what that is. How do I query pg_locks?
>
> SELECT * FROM pg_locks;
>
> http://www.postgresql.org/docs/7.4/static/monitoring-locks.html
>
> > > What version of PostgreSQL are you running?
> >
> > 7.4.8. Thank you for your help.
>
> Newer versions generally perform better; consider upgrading to 8.0
> or 8.1 if possible.
>
Removing the indexes, running the update SQL, and then adding back the
indexes worked much faster. Thank you for you help.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Slow update SQL
@ 2006-02-14 16:39 Markus Schaber <schabi@logix-tt.com>
parent: Ken Hill <ken@scottshill.com>
0 siblings, 0 replies; 8+ messages in thread
From: Markus Schaber @ 2006-02-14 16:39 UTC (permalink / raw)
To: ken@scottshill.com; pgsql-sql
Hi, Ken,
Ken Hill wrote:
> Removing the indexes, running the update SQL, and then adding back the
> indexes worked much faster. Thank you for you help.
It might be a good idea to run VACUUM FULL between updating and reindexing.
If you want to CLUSTER on an index, it will be best to create this index
first, then CLUSTER the table, and then recreate the other indices.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Slow update SQL
@ 2006-02-27 02:42 Bungsuputra Linan <blinan@jogjamedianet.com>
parent: Ken Hill <ken@scottshill.com>
1 sibling, 2 replies; 8+ messages in thread
From: Bungsuputra Linan @ 2006-02-27 02:42 UTC (permalink / raw)
To: ken@scottshill.com; pgsql-sql
Hi Ken,
I used to have the same problem. In my computer, using date_part in WHERE
clause will always slow down the system when the table has plenty of rows.
My suggestion is try to change the query to:
... WHERE dxdate >= '2001-01-01';
I hope this helps.
Regards,
bungsu
----- Original Message -----
From: Ken Hill
To: Postgres Help
Sent: Tuesday, February 14, 2006 8:48 AM
Subject: [SQL] Slow update SQL
I'm experiencing a very slow query. The table contains 611,564 rows of data.
I vaccumed the table:
VACUUM ANALYZE ncccr10;
SELECT count(*) FROM ncccr10;
count
--------
611564
(1 row)
When I try to analyze the query plan with:
EXPLAIN ANALYZE
UPDATE ncccr10
SET key = facilityno||'-'||
lastname||'-'||
sex||'-'||
ssno||'-'||
birthdate||'-'||
primarysit||'-'||
dxdate||'-'||
morphology3
WHERE date_part('year',dxdate) > '2000';
The query just never finishes (even 1 hour later). The colum key100 is
indexed, and I'm setting the value of this
column from other columns. Why is this so slow?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Slow update SQL
@ 2006-03-01 01:26 Ken Hill <ken@scottshill.com>
parent: Bungsuputra Linan <blinan@jogjamedianet.com>
1 sibling, 0 replies; 8+ messages in thread
From: Ken Hill @ 2006-03-01 01:26 UTC (permalink / raw)
To: Bungsuputra Linan <blinan@jogjamedianet.com>; +Cc: pgsql-sql
bungsu,
That does make the query work a bit faster. Thanks!
On Mon, 2006-02-27 at 09:42 +0700, Bungsuputra Linan wrote:
> Hi Ken,
>
> I used to have the same problem. In my computer, using date_part in WHERE
> clause will always slow down the system when the table has plenty of rows.
>
> My suggestion is try to change the query to:
> ... WHERE dxdate >= '2001-01-01';
>
> I hope this helps.
>
> Regards,
> bungsu
>
> ----- Original Message -----
> From: Ken Hill
> To: Postgres Help
> Sent: Tuesday, February 14, 2006 8:48 AM
> Subject: [SQL] Slow update SQL
>
>
> I'm experiencing a very slow query. The table contains 611,564 rows of data.
> I vaccumed the table:
>
> VACUUM ANALYZE ncccr10;
>
> SELECT count(*) FROM ncccr10;
> count
> --------
> 611564
> (1 row)
>
> When I try to analyze the query plan with:
>
> EXPLAIN ANALYZE
> UPDATE ncccr10
> SET key = facilityno||'-'||
> lastname||'-'||
> sex||'-'||
> ssno||'-'||
> birthdate||'-'||
> primarysit||'-'||
> dxdate||'-'||
> morphology3
> WHERE date_part('year',dxdate) > '2000';
>
> The query just never finishes (even 1 hour later). The colum key100 is
> indexed, and I'm setting the value of this
> column from other columns. Why is this so slow?
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Slow update SQL
@ 2006-03-01 10:17 Mauricio Fernandez A. <mfacontacto@ono.com>
parent: Bungsuputra Linan <blinan@jogjamedianet.com>
1 sibling, 0 replies; 8+ messages in thread
From: Mauricio Fernandez A. @ 2006-03-01 10:17 UTC (permalink / raw)
To: pgsql-sql
You can try this too:
...
WHERE EXTRACT(YEAR FROM dxdate::Date) > 2000
Mauricio Fernández A.
Ingeniero de Sistemas
Universidad Autónoma de Manizales (Colombia)
-----Mensaje original-----
De: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]En nombre de Bungsuputra Linan
Enviado el: lunes, 27 febrero, 2006 3:42
Para: ken@scottshill.com; Postgres Help
Asunto: Re: [SQL] Slow update SQL
Hi Ken,
I used to have the same problem. In my computer, using date_part in WHERE
clause will always slow down the system when the table has plenty of rows.
My suggestion is try to change the query to:
... WHERE dxdate >= '2001-01-01';
I hope this helps.
Regards,
bungsu
----- Original Message -----
From: Ken Hill
To: Postgres Help
Sent: Tuesday, February 14, 2006 8:48 AM
Subject: [SQL] Slow update SQL
I'm experiencing a very slow query. The table contains 611,564 rows of data.
I vaccumed the table:
VACUUM ANALYZE ncccr10;
SELECT count(*) FROM ncccr10;
count
--------
611564
(1 row)
When I try to analyze the query plan with:
EXPLAIN ANALYZE
UPDATE ncccr10
SET key = facilityno||'-'||
lastname||'-'||
sex||'-'||
ssno||'-'||
birthdate||'-'||
primarysit||'-'||
dxdate||'-'||
morphology3
WHERE date_part('year',dxdate) > '2000';
The query just never finishes (even 1 hour later). The colum key100 is
indexed, and I'm setting the value of this
column from other columns. Why is this so slow?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-03-01 10:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-14 01:48 Slow update SQL Ken Hill <ken@scottshill.com>
2006-02-14 02:14 ` Michael Fuhr <mike@fuhr.org>
2006-02-14 05:17 ` Michael Fuhr <mike@fuhr.org>
2006-02-14 16:35 ` Ken Hill <ken@scottshill.com>
2006-02-14 16:39 ` Markus Schaber <schabi@logix-tt.com>
2006-02-27 02:42 ` Bungsuputra Linan <blinan@jogjamedianet.com>
2006-03-01 01:26 ` Ken Hill <ken@scottshill.com>
2006-03-01 10:17 ` Mauricio Fernandez A. <mfacontacto@ono.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox