X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A4F4ED1E9C8 for ; Sat, 3 Apr 2004 21:31:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 63360-01 for ; Sat, 3 Apr 2004 17:31:31 -0400 (AST) Received: from gpdnet.co.uk (gpdnet.plus.com [212.56.100.243]) by svr1.postgresql.org (Postfix) with ESMTP id 7ACA1D1E9BB for ; Sat, 3 Apr 2004 17:31:14 -0400 (AST) Received: from Spooler by gpdnet.co.uk (Mercury/32 v4.01a) ID MO001E5D; 3 Apr 2004 22:35:28 +0100 Received: from spooler by gpdnet.co.uk (Mercury/32 v4.01a); 3 Apr 2004 22:35:13 +0100 Received: from gary (192.168.1.2) by gpdnet.co.uk (Mercury/32 v4.01a) with ESMTP ID MG001E5C; 3 Apr 2004 22:35:08 +0100 Resent-From: "Gary Doades" Resent-To: pgsql-performance@postgresql.org Resent-date: Sat, 03 Apr 2004 22:35:07 +0100 X-cs: R From: Gary Doades X-RS-ID: X-RS-Flags: 0,0,1,1,0,0,0 X-RS-Header: In-reply-to: <200404031338.i33DccbP015107@smtp.gvtc.com> X-RS-Header: References: <406EA51B.21218.D2DE061@localhost> X-RS-Sigset: 0 To: pgsql-performance-owner@postgresql.org Subject: Re: PostgreSQL and Linux 2.6 kernel. MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: 8BIT Date: Sat, 03 Apr 2004 21:20:49 +0100 Message-ID: Resent-Message-Id: <20040403213114.7ACA1D1E9BB@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/20 X-Sequence-Number: 6320 Thanks, I know about set showplan_text, but it is only the equivalent of explain, not explain analyze. The graphical plan gives full statistics, runtime, percentage cost, loop execution counts etc. which is much more useful. I don't know of a way of getting the graphical plan content in text form. Cheers, Gary. On 3 Apr 2004 at 6:50, @g v t c wrote: Use "Set Show_Plan" or something of the sort in Query Analyzer. Then run your SQL. This will change the graphical plan to a text plan similar to Postgresql or at least something close to readable. Gary Doades wrote: >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" 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 >> >> >> > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > -- 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