On 2 Apr 2004 at 22:36, [EMAIL PROTECTED] wrote:

OK, some more detail: 

Before wiping 2.4 off my test box for the second time: 

SQL Statement for update: 
update staff_booking set time_from = r.time_from from order_reqt r where r.reqt_id = 
staff_booking.reqt_id; 

Explain: (on 2.4) 
QUERY PLAN 
Merge Join  (cost=0.00..185731.30 rows=2845920 width=92) 
  Merge Cond: ("outer".reqt_id = "inner".reqt_id) 
  ->  Index Scan using order_reqt_pkey on order_reqt r  (cost=0.00..53068.20 
rows=2206291 width=6) 
  ->  Index Scan using staff_book_idx2 on staff_booking  (cost=0.00..99579.21 
rows=2845920 width=90) 

Total execution time: 18 hours 12 minutes 

vacuum full analyze: total time 3 hours 22 minutes 

Wait 2 hours for re-install 2.6, set params etc.  
restore database.  

Same SQL Statement 
Explain: (on 2.6) 
QUERY PLAN 
Merge Join  (cost=0.00..209740.24 rows=2845920 width=92) 
  Merge Cond: ("outer".reqt_id = "inner".reqt_id) 
  ->  Index Scan using order_reqt_pkey on order_reqt r  (cost=0.00..50734.20 
rows=2206291 width=6) 
  ->  Index Scan using staff_book_idx2 on staff_booking  (cost=0.00..117921.92 
rows=2845920 width=90) 

Total execution time: 2 hours 53 minutes 

vacuum full analyze: total time 1 hours 6 minutes 

Table definitions for the two tables involved: 
CREATE TABLE ORDER_REQT 
( 
        REQT_ID                 SERIAL, 
        ORDER_ID                integer NOT NULL, 
        DAYOFWEEK               smallint NOT NULL CHECK (DAYOFWEEK  
BETWEEN 0 AND 6), 
        TIME_FROM               smallint NOT NULL CHECK (TIME_FROM  
BETWEEN 0 AND 1439), 
        DURATION                smallint NOT NULL CHECK (DURATION  
BETWEEN 0 AND 1439), 
        PRODUCT_ID              integer NOT NULL, 
        NUMBER_REQT                     smallint NOT NULL DEFAULT (1), 
        WROPTIONS                       integer NOT NULL DEFAULT 0, 
        UID_REF                 integer NOT NULL, 
        DT_STAMP                timestamp NOT NULL DEFAULT  
current_timestamp, 
        Sentinel_Priority       integer NOT NULL DEFAULT 0, 
        PERIOD                  smallint NOT NULL DEFAULT 1 CHECK  
(PERIOD BETWEEN -2 AND 4), 
        FREQUENCY                       smallint NOT NULL DEFAULT 1, 
        PRIMARY KEY (REQT_ID) 
); 

CREATE TABLE STAFF_BOOKING 
( 
        BOOKING_ID              SERIAL, 
        REQT_ID                 integer NOT NULL, 
        ENTITY_TYPE             smallint NOT NULL DEFAULT 3  
check(ENTITY_TYPE in(3,4)), 
        STAFF_ID                integer NOT NULL, 
        CONTRACT_ID             integer NOT NULL, 
        TIME_FROM               smallint NOT NULL CHECK (TIME_FROM  
BETWEEN 0 AND 1439), 
        DURATION                smallint NOT NULL CHECK (DURATION  
BETWEEN 0 AND 1439), 
        PERIOD                  smallint NOT NULL DEFAULT 1 CHECK  
(PERIOD BETWEEN -2 AND 4), 
        FREQUENCY                       smallint NOT NULL DEFAULT 1, 
        TRAVEL_TO               smallint NOT NULL DEFAULT 0, 
        UID_REF                 integer NOT NULL, 
        DT_STAMP                timestamp NOT NULL DEFAULT  
current_timestamp, 
        SELL_PRICE              numeric(10,4) NOT NULL DEFAULT 0, 
        COST_PRICE              numeric(10,4) NOT NULL DEFAULT 0, 
        MIN_SELL_PRICE          numeric(10,4) NOT NULL DEFAULT 0, 
        MIN_COST_PRICE          numeric(10,4) NOT NULL DEFAULT 0, 
        Sentinel_Priority       integer NOT NULL DEFAULT 0, 
        CHECK_INTERVAL          smallint NOT NULL DEFAULT 0, 
      STATUS                    smallint NOT NULL DEFAULT 0, 
        WROPTIONS                       integer NOT NULL DEFAULT 0, 
        PRIMARY KEY (BOOKING_ID) 
); 

Foreign keys: 

ALTER TABLE ORDER_REQT ADD  
         FOREIGN KEY  
        ( 
                ORDER_ID 
        ) REFERENCES MAIN_ORDER ( 
                ORDER_ID 
        ) ON DELETE CASCADE; 

ALTER TABLE ORDER_REQT ADD  
         FOREIGN KEY  
        ( 
                PRODUCT_ID 
        ) REFERENCES PRODUCT ( 
                PRODUCT_ID 
        ); 

ALTER TABLE STAFF_BOOKING ADD  
         FOREIGN KEY  
        ( 
                CONTRACT_ID 
        ) REFERENCES STAFF_CONTRACT ( 
                CONTRACT_ID 
        ); 

ALTER TABLE STAFF_BOOKING ADD  
         FOREIGN KEY  
        ( 
                STAFF_ID 
        ) REFERENCES STAFF ( 
                STAFF_ID 
        ); 


Indexes: 

CREATE INDEX FK_IDX_ORDER_REQT  
         ON ORDER_REQT  
        ( 
                ORDER_ID 
        ); 

CREATE INDEX FK_IDX_ORDER_REQT_2 
         ON ORDER_REQT  
        ( 
                PRODUCT_ID 
        ); 

CREATE INDEX ORDER_REQT_IDX ON ORDER_REQT 
( 
        ORDER_ID, 
        PRODUCT_ID 
); 

CREATE INDEX ORDER_REQT_IDX4 ON ORDER_REQT 
( 
        REQT_ID, 
        TIME_FROM, 
        DURATION 
); 

CREATE INDEX FK_IDX_STAFF_BOOKING  
         ON STAFF_BOOKING  
        ( 
                CONTRACT_ID 
        ); 

CREATE INDEX FK_IDX_STAFF_BOOKING_2 
         ON STAFF_BOOKING  
        ( 
                STAFF_ID 
        ); 

CREATE INDEX STAFF_BOOK_IDX1 ON STAFF_BOOKING 
( 
        STAFF_ID, 
        REQT_ID 
); 

CREATE INDEX STAFF_BOOK_IDX2 ON STAFF_BOOKING 
( 
        REQT_ID 
); 

CREATE INDEX STAFF_BOOK_IDX3 ON STAFF_BOOKING 
( 
        BOOKING_ID, 
        REQT_ID 
); 


CREATE INDEX STAFF_BOOK_IDX4 ON STAFF_BOOKING 
( 
        BOOKING_ID, 
        CONTRACT_ID 
); 

There are no indexes on the columns involved in the update, they are  
not required for my usual select statements. This is an attempt to  
slightly denormalise the design to get the performance up comparable  
to SQL Server 2000. We hope to move some of our databases over to  
PostgreSQL later in the year and this is part of the ongoing testing.  
SQLServer's query optimiser is a bit smarter that PostgreSQL's (yet) 
so I am hand optimising some of the more frequently used  
SQL and/or tweaking the database design slightly. 

Later, after deciphering SQLServers graphical plans I will attempt to  
post comparitive performance/access plans, using the same data of  
course, if anyone would be interested.... 

Cheers, 
Gary. 



On 2 Apr 2004 at 1:32, Tom Lane wrote: 

> "Gary Doades" <[EMAIL PROTECTED]> writes: 
> > As a test in PosgreSQL I issued a statement to update a single column 
> > of a table containing 2.8 million rows with the values of a column in 
> > a table with similar rowcount.  Using the above spec I had to stop the 
> > server after 17 hours. The poor thing was thrashing the hard disk and 
> > doing more swapping than useful work. 
>  
> This statement is pretty much content-free, since you did not show us 
> the table schemas, the query, or the EXPLAIN output for the query. 
> (I'll forgive you the lack of EXPLAIN ANALYZE, but you could easily 
> have provided all the other hard facts.)  There's really no way to tell 
> where the bottleneck is.  Maybe it's a kernel-level issue, but I would 
> not bet on that without more evidence.  I'd definitely not bet on it 
> without direct confirmation that the same query plan was used in both 
> setups. 
>  
>                       regards, tom lane 
>  
> ---------------------------(end of broadcast)--------------------------- 
> TIP 3: if posting/reading through Usenet, please send an appropriate 
>       subscribe-nomail command to [EMAIL PROTECTED] so that your 
>       message can get through to the mailing list cleanly 
>  
>  
> --  
> Incoming mail is certified Virus Free. 
> Checked by AVG Anti-Virus (http://www.grisoft.com). 
> Version: 7.0.230 / Virus Database: 262.6.5 - Release Date: 31/03/2004 
>  



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to