Re: Temp tables... - Mailing list pgsql-general

On Tue, Jul 12, 2005 at 10:52:24AM -0700, Greg Patnude wrote:
>
> Performing an update to an inherited table system from inside of a stored
> procedure (PLPGSQL) seems to be unusually sluggish...

Is the update slower when done inside a function than when doing
it directly (e.g., from psql)?  That is, is the use of a function
relevant, or is the update equally slow in any case?  Could you
post the EXPLAIN ANALYZE output for the update?

The message subject is "Temp tables."  Are you using temporary
tables, and if so, are you seeing different behavior with temporary
tables than with "real" tables?  Again, is that relevant to the
problem?

> Does anyone have a faster solution ? I am updating 50 records and it
> takes approximately 4.375 seconds + or -....
>
> The inherited table has an ON INSERT DO INSTEAD and there are approximately
> 2 million rows in the inherited table structure...

Could you post the table definitions, including all indexes, rules,
etc.?  Do all the child tables have indexes on the column(s) used
to restrict the update?  As the documentation states, indexes aren't
inherited, so you might need to create additional indexes on the
children, indexes that you'd think would be redundant.  Example:

CREATE TABLE parent (id serial PRIMARY KEY);
CREATE TABLE child (x integer) INHERITS (parent);

INSERT INTO child (x) SELECT * FROM generate_series(1, 100000);

ANALYZE parent;
ANALYZE child;

EXPLAIN ANALYZE UPDATE child SET id = id WHERE id BETWEEN 1 AND 50;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on child  (cost=0.00..1991.00 rows=41 width=14) (actual time=0.059..307.234 rows=50 loops=1)
   Filter: ((id >= 1) AND (id <= 50))
 Total runtime: 309.350 ms
(3 rows)

EXPLAIN ANALYZE    UPDATE parent SET id = id WHERE id BETWEEN 1 AND 50;
                                                         QUERY PLAN
     

-----------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..2006.37 rows=52 width=14) (actual time=304.838..306.252 rows=50 loops=1)
   ->  Index Scan using parent_pkey on parent  (cost=0.00..15.37 rows=11 width=10) (actual time=0.110..0.110 rows=0
loops=1)
         Index Cond: ((id >= 1) AND (id <= 50))
   ->  Seq Scan on child parent  (cost=0.00..1991.00 rows=41 width=14) (actual time=304.705..305.619 rows=50 loops=1)
         Filter: ((id >= 1) AND (id <= 50))
 Total runtime: 307.935 ms
(6 rows)

Notice the sequential scans on child, even though we have an index
on parent.id, a column that child inherits.  We need to create an
index on child.id as well:

CREATE INDEX child_id_idx ON child (id);

EXPLAIN ANALYZE UPDATE child SET id = id WHERE id BETWEEN 1 AND 50;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using child_id_idx on child  (cost=0.00..3.65 rows=41 width=14) (actual time=0.369..1.371 rows=50 loops=1)
   Index Cond: ((id >= 1) AND (id <= 50))
 Total runtime: 6.100 ms
(3 rows)

EXPLAIN ANALYZE    UPDATE parent SET id = id WHERE id BETWEEN 1 AND 50;
                                                             QUERY PLAN
            

------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..19.02 rows=52 width=14) (actual time=0.119..1.895 rows=50 loops=1)
   ->  Index Scan using parent_pkey on parent  (cost=0.00..15.37 rows=11 width=10) (actual time=0.037..0.037 rows=0
loops=1)
         Index Cond: ((id >= 1) AND (id <= 50))
   ->  Index Scan using child_id_idx on child parent  (cost=0.00..3.65 rows=41 width=14) (actual time=0.066..1.320
rows=50loops=1) 
         Index Cond: ((id >= 1) AND (id <= 50))
 Total runtime: 7.820 ms
(6 rows)

If that's not the problem, then do other tables have foreign key
references to the table(s) you're updating?  If so, then you might
need indexes on the foreign key columns in the referring tables.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/