Re: PostgreSQL and Linux 2.6 kernel. - Mailing list pgsql-performance

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
>