Skip site navigation (1) Skip section navigation (2)

Recent 7.4 change slowed down a query by a factor of 3

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: pgsql-performance(at)postgresql(dot)org
Subject: Recent 7.4 change slowed down a query by a factor of 3
Date: 2003-06-18 15:02:10
Message-ID: 20030618150210.GA20603@wolff.to (view raw or flat)
Thread:
Lists: pgsql-performance
The query below was running in a bit under 300ms on a version of 7.4
from less than a week ago until I updated to the version from last night.
Now it takes about 800ms using a significantly different plan.
The query is:
explain analyze
  select count(1)
    from
      (select distinct on (areaid) touched
        from crate
        order by areaid desc, touched desc)
        as current
    where touched >= localtimestamp + '10 year ago'
    group by touched >= localtimestamp + '2 year ago'
    order by touched >= localtimestamp + '2 year ago' desc;

I don't have the earlier version of 7.4 around, but I get the better plan
in 7.3.3.
                                version                                 
------------------------------------------------------------------------
 PostgreSQL 7.4devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1756.33..1756.50 rows=67 width=19) (actual time=795.64..795.65 rows=2 loops=1)
   Sort Key: (touched >= (('now'::text)::timestamp(6) without time zone + '-2 years'::interval))
   ->  HashAggregate  (cost=1753.46..1754.30 rows=67 width=19) (actual time=795.48..795.48 rows=2 loops=1)
         ->  Subquery Scan current  (cost=1624.62..1737.38 rows=3216 width=19) (actual time=631.84..784.75 rows=5339 loops=1)
               Filter: (touched >= (('now'::text)::timestamp(6) without time zone + '-10 years'::interval))
               ->  Unique  (cost=1624.62..1705.22 rows=3216 width=19) (actual time=631.72..713.66 rows=5364 loops=1)
                     ->  Sort  (cost=1624.62..1664.92 rows=16119 width=19) (actual time=631.72..639.77 rows=16119 loops=1)
                           Sort Key: areaid, touched
                           ->  Seq Scan on crate  (cost=0.00..498.19 rows=16119 width=19) (actual time=0.02..48.85 rows=16119 loops=1)
 Total runtime: 800.88 msec
(10 rows)

                      Table "public.crate"
 Column  |            Type             |       Modifiers        
---------+-----------------------------+------------------------
 areaid  | text                        | not null
 gameid  | text                        | not null
 rate    | integer                     | not null default 5000
 frq     | integer                     | not null default 0
 opp     | integer                     | not null default 0
 rmp     | integer                     | not null default 0
 trn     | integer                     | not null default 0
 rp      | text                        | 
 gm      | text                        | 
 touched | timestamp without time zone | not null default 'now'
Indexes:
    "crate_pkey" PRIMARY KEY btree (areaid, gameid),
    "crate_game" btree (gameid, areaid),
    "crate_touched" btree (areaid, touched)
Check Constraints:
    "rate_nonnegative" CHECK (rate >= 0),
    "rate_other_interested" CHECK ((frq > 0) OR (rate = 5000)),
    "frq_nonnegative" CHECK (frq >= 0),
    "opp_nonnegative" CHECK (opp >= 0),
    "rmp_nonnegative" CHECK (rmp >= 0),
    "trn_nonnegative" CHECK (trn >= 0)
Foreign Key Constraints:
    "bad_areaid" FOREIGN KEY (areaid) REFERENCES cname(areaid),
    "bad_gameid" FOREIGN KEY (gameid) REFERENCES games(gameid)

                               version                               
---------------------------------------------------------------------
 PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1094.46..1094.87 rows=161 width=19) (actual time=274.17..274.18 rows=2 loops=1)
   Sort Key: (touched >= (('now'::text)::timestamp(6) without time zone + '-2 years'::interval))
   ->  Aggregate  (cost=1076.46..1088.55 rows=161 width=19) (actual time=263.78..274.09 rows=2 loops=1)
         ->  Group  (cost=1076.46..1084.52 rows=1612 width=19) (actual time=255.12..269.69 rows=5339 loops=1)
               ->  Sort  (cost=1076.46..1080.49 rows=1612 width=19) (actual time=255.11..258.09 rows=5339 loops=1)
                     Sort Key: (touched >= (('now'::text)::timestamp(6) without time zone + '-2 years'::interval))
                     ->  Subquery Scan current  (cost=0.00..990.59 rows=1612 width=19) (actual time=0.12..240.81 rows=5339 loops=1)
                           Filter: (touched >= (('now'::text)::timestamp(6) without time zone + '-10 years'::interval))
                           ->  Unique  (cost=0.00..990.59 rows=1612 width=19) (actual time=0.04..159.11 rows=5364 loops=1)
                                 ->  Index Scan Backward using crate_touched on crate  (cost=0.00..950.30 rows=16119 width=19) (actual time=0.04..82.15 rows=16119 loops=1)
 Total runtime: 275.32 msec
(11 rows)

                      Table "public.crate"
 Column  |            Type             |       Modifiers        
---------+-----------------------------+------------------------
 areaid  | text                        | not null
 gameid  | text                        | not null
 rate    | integer                     | not null default 5000
 frq     | integer                     | not null default 0
 opp     | integer                     | not null default 0
 rmp     | integer                     | not null default 0
 trn     | integer                     | not null default 0
 rp      | text                        | 
 gm      | text                        | 
 touched | timestamp without time zone | not null default 'now'
Indexes: crate_pkey primary key btree (areaid, gameid),
         crate_game btree (gameid, areaid),
         crate_touched btree (areaid, touched)
Check constraints: "trn_nonnegative" (trn >= 0)
                   "rmp_nonnegative" (rmp >= 0)
                   "opp_nonnegative" (opp >= 0)
                   "frq_nonnegative" (frq >= 0)
                   "rate_other_interested" ((frq > 0) OR (rate = 5000))
                   "rate_nonnegative" (rate >= 0)
Foreign Key constraints: bad_gameid FOREIGN KEY (gameid) REFERENCES games(gameid) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         bad_areaid FOREIGN KEY (areaid) REFERENCES cname(areaid) ON UPDATE NO ACTION ON DELETE NO ACTION


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-06-18 15:18:39
Subject: Re: Recent 7.4 change slowed down a query by a factor of 3
Previous:From: SZŰCS GáborDate: 2003-06-18 07:11:25
Subject: Re: Postgres Connections Requiring Large Amounts of Memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group