Re: Very slow update + not using clustered index

Tom-

Thanks for the quick response. More details are inline.

-mike

On Thu, 01 Jan 2004 23:06:11 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mike Glover <mpg4(at)duluoz(dot)net> writes:

> AFAICS these plans are identical, and therefore the difference in
> runtime must be ascribed to the time spent actually doing the updates.
> It seems unlikely that the raw row inserts and updating the single
> index could be quite that slow --- perhaps you have a foreign key
> or trigger performance problem?

There are no foreign keys or triggers for either of the tables.

> Is this PG 7.4?

Yes, PG 7.4

>
> A quick experiment shows that if the planner does not have any reason
> to prefer one ordering over another, the current coding will put the
> last WHERE clause first:
[snip]>
> and so you could probably improve matters just by switching the order
> of your WHERE clauses. Of course this answer will break as soon as
> anyone touches any part of the related code, so I'd like to try to fix
> it so that there is actually a principled choice made. Could you send
> along the pg_stats rows for these columns?
>

It looks like the planner is already making a principled choice:

bookshelf=> explain select s.* from summary s, inventory i where s.isbn
= i.isbn and s.price_min = i.price;
QUERY PLAN
-----------------------------------------------------------------------
Merge Join (cost=491180.66..512965.72 rows=9237 width=58)
Merge Cond: (("outer".price_min = "inner".price)
AND ("outer"."?column8?" = "inner"."?column3?"))
-> Sort (cost=361887.05..367000.05 rows=2045201 width=58)
Sort Key: s.price_min, (s.isbn)::text
-> Seq Scan on summary s (cost=0.00..44651.01 rows=2045201
width=58)
-> Sort (cost=129293.61..131499.09 rows=882192 width=25)
Sort Key: i.price, (i.isbn)::text
-> Seq Scan on inventory i (cost=0.00..16173.92 rows=882192
width=25)
(8 rows)

bookshelf=> explain select s.* from summary s, inventory i where
s.price_min = i.price and s.isbn = i.isbn;
QUERY PLAN
-----------------------------------------------------------------------
Merge Join (cost=491180.66..512965.72 rows=9237 width=58)
Merge Cond: (("outer".price_min = "inner".price) AND
("outer"."?column8?" ="inner"."?column3?"))
-> Sort (cost=361887.05..367000.05 rows=2045201 width=58)
Sort Key: s.price_min, (s.isbn)::text
-> Seq Scan on summary s (cost=0.00..44651.01 rows=2045201
width=58)
-> Sort(cost=129293.61..131499.09 rows=882192 width=25)
Sort Key: i.price, (i.isbn)::text
-> Seq Scan on inventory i (cost=0.00..16173.92 rows=882192
width=25)
(8 rows)

Here are the pg_stats rows:
bookshelf=> select * from pg_stats where schemaname='de' and
tablename='inventory' and attname='isbn'; schemaname | tablename |
attname | null_frac | avg_width | n_distinct | most_common_vals |
most_common_freqs |
histogram_bounds |
correlation
------------+-----------+---------+-----------+-----------+------------
+------------------+-------------------+-------------------------------
-----------------------------------------------------------------------
----------------------+------------- de | inventory | isbn |
0 | 14 | -1 | |
|
{0002551543,0198268211,0375507299,0486231305,0673395197,0767901576,0810
304430,0865738890,0931595029,1574160052,9971504014} | 1(1 row)

bookshelf=> select * from pg_stats where schemaname='de' and
tablename='inventory' and attname='price'; schemaname | tablename |
attname | null_frac | avg_width | n_distinct |
most_common_vals |
most_common_freqs |
histogram_bounds |
correlation
------------+-----------+---------+-----------+-----------+------------
+--------------------------------------------------------------+-------
-----------------------------------------------------------------------
-----------------------+-----------------------------------------------
--------------------------+------------- de | inventory | price
| 0 | 11 | 1628 |
{59.95,0.00,54.88,53.30,60.50,64.25,73.63,49.39,50.02,53.37} |
{0.259667,0.00633333,0.00533333,0.00466667,0.00466667,0.00466667,0.0046
6667,0.00433333,0.004,0.004} |
{49.16,52.06,55.53,59.56,63.78,68.90,76.90,88.53,106.16,143.75,1538.88}
| 0.149342(1 row)

bookshelf=> select * from pg_stats where schemaname='de' and
tablename='summary' and attname='isbn'; schemaname | tablename | attname
| null_frac | avg_width | n_distinct | most_common_vals |
most_common_freqs |
histogram_bounds |
correlation
------------+-----------+---------+-----------+-----------+------------
+------------------+-------------------+-------------------------------
-----------------------------------------------------------------------
----------------------+------------- de | summary | isbn |
0 | 14 | -1 | |
|
{0001984209,020801912X,0395287693,055214911X,0722525915,0787630896,0822
218100,0883856263,1413900275,1843910381,9999955045} | 1(1 row)

bookshelf=> select * from pg_stats where schemaname='de' and
tablename='summary' and attname='price_min'; schemaname | tablename |
attname | null_frac | avg_width | n_distinct |
most_common_vals |
most_common_freqs |
histogram_bounds |
correlation
------------+-----------+-----------+-----------+-----------+----------
--+---------------------------------------------------------+----------
-----------------------------------------------------------------------
-------------------+---------------------------------------------------
------------------+------------- de | summary | price_min |
0 | 10 | 1532 |
{0.00,59.95,6.95,6.00,4.07,10.17,11.53,10.85,4.75,8.81} |
{0.425333,0.029,0.0193333,0.00533333,0.00333333,0.00333333,0.00333333,0
.003,0.00266667,0.00266667} |
{0.05,7.11,10.30,14.28,19.54,27.86,50.47,61.25,76.44,104.79,744.73} |
0.0546667(1 row)

(mangled a bit by the auto-linewrap, I'm afraid)

> > The second question is: why, oh why does the update take such and
> > obscenely long time to complete?
>
> See above --- the problem is not within the plan, but must be sought
> elsewhere.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Mike Glover
Key ID BFD19F2C <mpg4(at)duluoz(dot)net>