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@sss.pgh.pa.us> wrote:

> Mike Glover <mpg4@duluoz.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@duluoz.net>