Обсуждение: Another weird one with an UPDATE

----- Original Message -----

From: David Griffiths

To: pgsql-performance@postgresql.org

Sent: Saturday, October 11, 2003 12:44 PM

Subject: [PERFORM] Another weird one with an UPDATE

I am running an update-query to benchmark various databases; the postgres version is,

 

UPDATE user_account SET last_name = 'abc'
WHERE user_account_id IN (SELECT user_account_id FROM commercial_entity, commercial_service WHERE yw_account_id IS NULL
AND commercial_entity.commercial_entity_id = commercial_service.commercial_entity_id);

 

 

The inner query (the select), run by itself, takes about a second. Add the outer query (the update-portion), and the query dies. The machine has been vacuum-analzyed. Here is the explain-analyze:

 

benchtest=# EXPLAIN ANALYZE UPDATE user_account SET last_name = 'abc'
benchtest-# WHERE user_account_id IN (SELECT user_account_id FROM commercial_entity, commercial_service WHERE yw_account_id IS NULL
benchtest(# AND commercial_entity.commercial_entity_id = commercial_service.commercial_entity_id);

 Seq Scan on user_account  (cost=0.00..813608944.88 rows=36242 width=718) (actual time=15696258.98..16311130.29 rows=3075 loops=1)   Filter: (subplan)
   SubPlan
     ->  Materialize  (cost=11224.77..11224.77 rows=86952 width=36) (actual time=0.06..106.40 rows=84831 loops=72483)
           ->  Merge Join  (cost=0.00..11224.77 rows=86952 width=36) (actual time=0.21..1845.13 rows=85158 loops=1)
                 Merge Cond: ("outer".commercial_entity_id = "inner".commercial_entity_id)
                 ->  Index Scan using commercial_entity_pkey on commercial_entity  (cost=0.00..6787.27 rows=77862 width=24) (actual time=0.06..469.56 rows=78132 loops=1)
                       Filter: (yw_account_id IS NULL)
                 ->  Index Scan using comm_serv_comm_ent_id_i on commercial_service  (cost=0.00..2952.42 rows=88038 width=12) (actual time=0.03..444.80 rows=88038 loops=1)
 Total runtime: 16332976.21 msec
(10 rows)

 

Here are the relevant parts of the schema:

 

 

 

USER_ACCOUNT

 

            Column             |            Type             |          Modifiers
-------------------------------+-----------------------------+-----------------------------
 user_account_id               | numeric(10,0)               | not null
 first_name                    | character varying(100)      |
 last_name                     | character varying(100)      |
Indexes: user_account_pkey primary key btree (user_account_id),
         usr_acc_last_name_i btree (last_name),
Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES lang(lang_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         $2 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         $3 FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION
 

 

 

COMMERCIAL_ENTITY

 

          Column           |            Type             |                          Modifiers
---------------------------+-----------------------------+-------------------------------------------------------------
 commercial_entity_id      | numeric(10,0)               | not null
 yw_account_id             | numeric(10,0)               |
Indexes: commercial_entity_pkey primary key btree (commercial_entity_id),
         comm_ent_yw_acc_id_i btree (yw_account_id)
Foreign Key constraints: $1 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         $2 FOREIGN KEY (user_account_id) REFERENCES user_account(user_account_id) ON UPDATE NO ACTION ON DELETE NO ACTION

 

 

 

COMMERCIAL_SERVICE

 

        Column        |     Type      | Modifiers
----------------------+---------------+-----------
 commercial_entity_id | numeric(10,0) | not null
 service_type_id      | numeric(10,0) | not null
 source_id            | numeric(10,0) | not null
Indexes: commercial_service_pkey primary key btree (commercial_entity_id, service_type_id),
         comm_serv_comm_ent_id_i btree (commercial_entity_id),
         comm_serv_serv_type_id_i btree (service_type_id),
         comm_serv_source_id_i btree (source_id)
Foreign Key constraints: $1 FOREIGN KEY (commercial_entity_id) REFERENCES commercial_entity(commercial_entity_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         $2 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         $3 FOREIGN KEY (service_type_id) REFERENCES service_type(service_type_id) ON UPDATE NO ACTION ON DELETE NO ACTION

 

 

Here is the postgres.conf (or the variables that are not commented out):

 

tcpip_socket = true
max_connections = 500

shared_buffers = 32768          # min max_connections*2 or 16, 8KB each
wal_buffers = 128               # min 4, typically 8KB each

sort_mem = 4096                 # min 64, size in KB
effective_cache_size = 50000    # typically 8KB each

 

Is it a problem with "IN"?

 

David

On Sat, 11 Oct 2003, David Griffiths wrote:

> Sorry - just found the FAQ (
> http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22
> <http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22> ) on how
> IN is very slow.
>
> So I rewrote the query:
>
> \o ./data/temp.txt
> SELECT current_timestamp;
> UPDATE user_account SET last_name = 'abc'
> WHERE EXISTS (SELECT ua.user_account_id FROM user_account ua,
> commercial_entity ce, commercial_service cs
> WHERE ua.user_account_id = ce.user_account_id AND
> ce.commercial_entity_id = cs.commercial_entity_id);
> SELECT current_timestamp;

I don't think that's the query you want.  You're not binding the subselect
to the outer values of user_account.

I think you want something like:
UPDATE user_account SET last_name = 'abc'
 WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
 WHERE user_account.user_account_id = ce.user_account_id AND
 ce.commercial_entity_id = cs.commercial_entity_id);

Thanks - that worked.

David
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "David Griffiths" <dgriffiths@boats.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Saturday, October 11, 2003 3:34 PM
Subject: Re: [PERFORM] Another weird one with an UPDATE


> On Sat, 11 Oct 2003, David Griffiths wrote:
>
> > Sorry - just found the FAQ (
> > http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22
> > <http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22> ) on how
> > IN is very slow.
> >
> > So I rewrote the query:
> >
> > \o ./data/temp.txt
> > SELECT current_timestamp;
> > UPDATE user_account SET last_name = 'abc'
> > WHERE EXISTS (SELECT ua.user_account_id FROM user_account ua,
> > commercial_entity ce, commercial_service cs
> > WHERE ua.user_account_id = ce.user_account_id AND
> > ce.commercial_entity_id = cs.commercial_entity_id);
> > SELECT current_timestamp;
>
> I don't think that's the query you want.  You're not binding the subselect
> to the outer values of user_account.
>
> I think you want something like:
> UPDATE user_account SET last_name = 'abc'
>  WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
>  WHERE user_account.user_account_id = ce.user_account_id AND
>  ce.commercial_entity_id = cs.commercial_entity_id);

[snip]

> I think you want something like:
> UPDATE user_account SET last_name = 'abc'
>  WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
>  WHERE user_account.user_account_id = ce.user_account_id AND
>  ce.commercial_entity_id = cs.commercial_entity_id);

Unfort, this is still taking a long time.

----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------
 Seq Scan on user_account  (cost=0.00..748990.51 rows=36242 width=716)
(actual time=10262.50..26568.03 rows=3771 loops=1)
   Filter: (subplan)
   SubPlan
     ->  Nested Loop  (cost=0.00..11.47 rows=1 width=24) (actual
time=0.24..0.24 rows=0 loops=72483)
           ->  Index Scan using comm_ent_usr_acc_id_i on commercial_entity
ce  (cost=0.00..4.12 rows=1 width=12) (actual time=0.05..0.05 rows=0
loops=72483)
                 Index Cond: ($0 = user_account_id)
           ->  Index Scan using comm_serv_comm_ent_id_i on
commercial_service cs  (cost=0.00..7.32 rows=3 width=12) (actual
time=1.72..1.72 rows=0 loops=7990)
                 Index Cond: ("outer".commercial_entity_id =
cs.commercial_entity_id)
 Total runtime: 239585.09 msec
(9 rows)

Anyone have any thoughts?

David

David Griffiths wrote:
>>I think you want something like:
>>UPDATE user_account SET last_name = 'abc'
>> WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
>> WHERE user_account.user_account_id = ce.user_account_id AND
>> ce.commercial_entity_id = cs.commercial_entity_id);
>
> Unfort, this is still taking a long time.
> -------
>  Seq Scan on user_account  (cost=0.00..748990.51 rows=36242 width=716)

Do you have an index on user_account.user_account_id?

Joe

On Sun, 12 Oct 2003, David Griffiths wrote:

> [snip]
>
> > I think you want something like:
> > UPDATE user_account SET last_name = 'abc'
> >  WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
> >  WHERE user_account.user_account_id = ce.user_account_id AND
> >  ce.commercial_entity_id = cs.commercial_entity_id);
>
> Unfort, this is still taking a long time.

Hmm, does
UPDATE user_account SET last_name='abc'
 FROM commercial_entity ce, commercial_service cs
 WHERE user_account.user_account_id = ce.user_account_id AND
  ce.commercial_entity_id=cs.commercial_entity_id;
give the right results... That might end up being faster.

Yes, the query operates only on indexed columns (all numeric(10)'s).

            Column             |            Type             |
Modifiers
-------------------------------+-----------------------------+--------------
---------------
 user_account_id               | numeric(10,0)               | not null
[snip]
Indexes: user_account_pkey primary key btree (user_account_id),
Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES lang(lang_id)
ON UPDATE NO ACTION ON DELETE NO ACTION,
                         $2 FOREIGN KEY (source_id) REFERENCES
source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         $3 FOREIGN KEY (user_role_id) REFERENCES
user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION

David

----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "David Griffiths" <dgriffiths@boats.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Sunday, October 12, 2003 6:37 PM
Subject: Re: [PERFORM] Another weird one with an UPDATE


> David Griffiths wrote:
> >>I think you want something like:
> >>UPDATE user_account SET last_name = 'abc'
> >> WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
> >> WHERE user_account.user_account_id = ce.user_account_id AND
> >> ce.commercial_entity_id = cs.commercial_entity_id);
> >
> > Unfort, this is still taking a long time.
> > -------
> >  Seq Scan on user_account  (cost=0.00..748990.51 rows=36242 width=716)
>
> Do you have an index on user_account.user_account_id?
>
> Joe

It's a slight improvement, but that could be other things as well.

I'd read that how you tune Postgres will determine how the optimizer works
on a query (sequential scan vs index scan). I am going to post all I've done
with tuning tommorow, and see if I've done anything dumb. I've found some
contradictory advice, and I'm still a bit hazy on how/why Postgres trusts
the OS to do caching. I'll post it all tommorow.

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------
 Merge Join  (cost=11819.21..15258.55 rows=12007 width=752) (actual
time=4107.64..5587.81 rows=20880 loops=1)
   Merge Cond: ("outer".commercial_entity_id = "inner".commercial_entity_id)
   ->  Index Scan using comm_serv_comm_ent_id_i on commercial_service cs
(cost=0.00..3015.53 rows=88038 width=12) (actual time=0.05..487.23
rows=88038 loops=1)
   ->  Sort  (cost=11819.21..11846.08 rows=10752 width=740) (actual
time=3509.07..3955.15 rows=25098 loops=1)
         Sort Key: ce.commercial_entity_id
         ->  Merge Join  (cost=0.00..9065.23 rows=10752 width=740) (actual
time=0.18..2762.13 rows=7990 loops=1)
               Merge Cond: ("outer".user_account_id =
"inner".user_account_id)
               ->  Index Scan using user_account_pkey on user_account
(cost=0.00..8010.39 rows=72483 width=716) (actual time=0.05..2220.86
rows=72483 loops=1)
               ->  Index Scan using comm_ent_usr_acc_id_i on
commercial_entity ce  (cost=0.00..4787.69 rows=78834 width=24) (actual
time=0.02..55.64 rows=7991 loops=1)
 Total runtime: 226239.77 msec
(10 rows)

David

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "David Griffiths" <dgriffiths@boats.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Sunday, October 12, 2003 6:48 PM
Subject: Re: [PERFORM] Another weird one with an UPDATE


> On Sun, 12 Oct 2003, David Griffiths wrote:
>
> > [snip]
> >
> > > I think you want something like:
> > > UPDATE user_account SET last_name = 'abc'
> > >  WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service
cs
> > >  WHERE user_account.user_account_id = ce.user_account_id AND
> > >  ce.commercial_entity_id = cs.commercial_entity_id);
> >
> > Unfort, this is still taking a long time.
>
> Hmm, does
> UPDATE user_account SET last_name='abc'
>  FROM commercial_entity ce, commercial_service cs
>  WHERE user_account.user_account_id = ce.user_account_id AND
>   ce.commercial_entity_id=cs.commercial_entity_id;
> give the right results... That might end up being faster.

David Griffiths wrote:

> It's a slight improvement, but that could be other things as well.
>
> I'd read that how you tune Postgres will determine how the optimizer works
> on a query (sequential scan vs index scan). I am going to post all I've done
> with tuning tommorow, and see if I've done anything dumb. I've found some
> contradictory advice, and I'm still a bit hazy on how/why Postgres trusts
> the OS to do caching. I'll post it all tommorow.
>
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------
>  Merge Join  (cost=11819.21..15258.55 rows=12007 width=752) (actual
> time=4107.64..5587.81 rows=20880 loops=1)
>    Merge Cond: ("outer".commercial_entity_id = "inner".commercial_entity_id)
>    ->  Index Scan using comm_serv_comm_ent_id_i on commercial_service cs
> (cost=0.00..3015.53 rows=88038 width=12) (actual time=0.05..487.23
> rows=88038 loops=1)
>    ->  Sort  (cost=11819.21..11846.08 rows=10752 width=740) (actual
> time=3509.07..3955.15 rows=25098 loops=1)
>          Sort Key: ce.commercial_entity_id

I think this is the problem. Is there an index on ce.commercial_entity_id?

>          ->  Merge Join  (cost=0.00..9065.23 rows=10752 width=740) (actual
> time=0.18..2762.13 rows=7990 loops=1)
>                Merge Cond: ("outer".user_account_id =
> "inner".user_account_id)
>                ->  Index Scan using user_account_pkey on user_account
> (cost=0.00..8010.39 rows=72483 width=716) (actual time=0.05..2220.86
> rows=72483 loops=1)
>                ->  Index Scan using comm_ent_usr_acc_id_i on
> commercial_entity ce  (cost=0.00..4787.69 rows=78834 width=24) (actual
> time=0.02..55.64 rows=7991 loops=1)

In this case of comparing account ids, its using two index scans. In the entity
field though, it chooses a sort. I think there is an index missing. The costs
are also shot up as well.

>  Total runtime: 226239.77 msec

Standard performance question. What was the last time these tables/database were
vacuumed. Have you tuned postgresql.conf correctly?

  HTH

  Shridhar

David Griffiths wrote:
> Yes, the query operates only on indexed columns (all numeric(10)'s).
>
>             Column             |            Type             |
> Modifiers
> -------------------------------+-----------------------------+--------------
> ---------------
>  user_account_id               | numeric(10,0)               | not null
> [snip]
> Indexes: user_account_pkey primary key btree (user_account_id),
> Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES lang(lang_id)
> ON UPDATE NO ACTION ON DELETE NO ACTION,
>                          $2 FOREIGN KEY (source_id) REFERENCES
> source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
>                          $3 FOREIGN KEY (user_role_id) REFERENCES
> user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION

And what about commercial_entity.user_account_id. Is it indexed and what
is its data type (i.e. does it match numeric(10,0))?

Also, have you run VACUUM ANALYZE lately?

Joe

> And what about commercial_entity.user_account_id. Is it indexed and what
> is its data type (i.e. does it match numeric(10,0))?

Yup - all columns in the statement are indexed, and they are all
numeric(10,0).

> Also, have you run VACUUM ANALYZE lately?

Yup - just before the last run.

Will get together my tuning data now.

David