pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Doades <gpd@gpdnet.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL and Linux 2.6 kernel.
Date: Sat, 03 Apr 2004 11:50:51 +0100
Message-ID: <406EA51B.21218.D2DE061@localhost> (raw)

On 2 Apr 2004 at 22:36, pgsql-performance@postgresql. 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" <gpd@gpdnet.co.uk> 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 majordomo@postgresql.org 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 
>  





view thread (34+ messages)  latest in thread

Message-ID: <406EA51B.21218.D2DE061@localhost>
Permalink:  ../406EA51B.21218.D2DE061@localhost/
Also on:    postgresql.org/message-id/406EA51B.21218.D2DE061@localhost

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gpd@gpdnet.co.uk
  Subject: Re: PostgreSQL and Linux 2.6 kernel.
  In-Reply-To: <406EA51B.21218.D2DE061@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox