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.
Advertising
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, [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])
>
>
>
>
--
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 8: explain analyze is your friend
