Re: PostgreSQL and Linux 2.6 kernel.

On 2 Apr 2004 at 22:36, pgsql-performance(at)postgresql(dot) 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(at)gpdnet(dot)co(dot)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(at)postgresql(dot)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
>