Date: Mon, 26 Sep 2011 11:39:01 +0200
From: Marc Cousin <cousinmarc@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: overzealous sorting?
Message-ID: <20110926113901.38fdf892@marco-dalibo>
In-Reply-To: <201109261628.15363.anthony.shipman@symstream.com>
References: <201109261628.15363.anthony.shipman@symstream.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Le Mon, 26 Sep 2011 16:28:15 +1000,
anthony.shipman@symstream.com a =C3=A9crit :

> In Mammoth Replicator (PG 8.3) I have a table described as=20
>=20
>                                        Table "public.tevent_cdr"
>      Column     |           Type           |
> Modifiers
> ----------------+--------------------------+-----------------------------=
-------------------------------
> event_id       | integer                  | not null default
> nextval(('event_id_seq'::text)::regclass) timestamp      | timestamp
> with time zone | not null classification | character varying        |
> not null area           | character varying        | not null
>  kind           | character varying        |
>  device_id      | integer                  |
>  device_name    | character varying        |
>  fleet_id       | integer                  |
>  fleet_name     | character varying        |
>  customer_id    | integer                  |
>  customer_name  | character varying        |
>  event          | text                     |
> Indexes:
>     "tevent_cdr_event_id" UNIQUE, btree (event_id)
>     "tevent_cdr_timestamp" btree ("timestamp")
> Check constraints:
>     "tevent_cdr_classification_check" CHECK (classification::text=20
> =3D 'cdr'::text)
> Inherits: tevent
>=20
>=20
> This simple query puzzles me. Why does it need to sort the records?
> Don't they come from the index in order?
>=20
>  "explain analyze select * from tevent_cdr where timestamp >=3D=20
> '2011-09-09 12:00:00.000000+0' and timestamp < '2011-09-09=20
> 13:00:00.000000+0' and classification =3D 'cdr' order by timestamp;"
>                                                                          =
      =20
> QUERY PLAN
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------
>   Sort  (cost=3D9270.93..9277.12 rows=3D2477 width=3D588) (actual=20
> time=3D9.219..11.489 rows=3D2480 loops=3D1)
>     Sort Key: "timestamp"
>     Sort Method:  quicksort  Memory: 2564kB
>     ->  Bitmap Heap Scan on tevent_cdr  (cost=3D57.93..9131.30
> rows=3D2477 width=3D588) (actual time=3D0.440..3.923 rows=3D2480 loops=3D=
1)
>           Recheck Cond: (("timestamp" >=3D '2011-09-09=20
> 22:00:00+10'::timestamp with time zone) AND ("timestamp" <
> '2011-09-09 23:00:00+10'::timestamp with time zone))
>           Filter: ((classification)::text =3D 'cdr'::text)
>           ->  Bitmap Index Scan on tevent_cdr_timestamp =20
> (cost=3D0.00..57.31 rows=3D2477 width=3D0) (actual time=3D0.404..0.404
> rows=3D2480 loops=3D1)
>                 Index Cond: (("timestamp" >=3D '2011-09-09=20
> 22:00:00+10'::timestamp with time zone) AND ("timestamp" <
> '2011-09-09 23:00:00+10'::timestamp with time zone))
>   Total runtime: 13.847 ms
> (9 rows)

Because Index Scans are sorted, not Bitmap Index Scans, which builds a
list of pages to visit, to be then visited by the Bitmap Heap Scan step.

Marc.