From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: PG trouble with index-usage in sort
Date: Mon, 26 Nov 2007 22:08:01 +0100
User-Agent: KMail/1.9.6 (enterprise 0.20070907.709405)
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200711262208.02000.andreak@officenet.no>

Hi all.

I'm seeing something fishy when trying to self-join two large tables and th=
en=20
order by one column.

I have the following schema:

CREATE TABLE origo_person(
id SERIAL PRIMARY KEY,
firstname varchar,
lastname varchar,
created timestamp not null default now(),
created_by integer REFERENCES onp_user(id),
onp_user_id integer references onp_user(id)
);

create index origo_person_created_idx on origo_person(created);
create index origo_person_createdby_idx on origo_person(created_by);
create index origo_person_onp_user_id_idx on origo_person(onp_user_id);
create index origo_person_firstname_idx on origo_person(firstname);

insert into onp_user(id) values (1);

copy origo_person (firstname, lastname, created)=20
from '/home/andreak/simpleperson.sql';

update origo_person set created_by =3D 1;

update origo_person set onp_user_id =3D 1 where id =3D 1;

simpleperson.sql has more than 200K entries in COPY-format:
$ tail -5 /home/andreak/simpleperson.sql
INGOLF  KALLEBERG       2007-08-21 22:23:43.571421
SIGRUNN BRUVIK  2007-08-21 22:23:43.571421
ELFRID  FROGNER 2007-08-21 22:23:43.571421
GUNNAR KRISTOFFER       DOVLAND 2007-08-21 22:23:43.571421
JAN ARNE        HAARR   2007-08-21 22:23:43.571421

Now, the two first queries are *fast*, but the 3rd query is slow:
1. Fast:
EXPLAIN ANALYZE SELECT p.firstname, p.lastname FROM origo_person p order by=
=20
p.firstname ASC limit 5;
                                                                        QUE=
RY=20
PLAN
=2D------------------------------------------------------------------------=
=2D---
 Limit  (cost=3D0.00..0.55 rows=3D5 width=3D17) (actual time=3D0.031..0.070=
 rows=3D5=20
loops=3D1)
   ->  Index Scan using origo_person_firstname_idx on origo_person p =20
(cost=3D0.00..22277.13 rows=3D200827 width=3D17) (actual time=3D0.025..0.04=
6 rows=3D5=20
loops=3D1)
 Total runtime: 0.128 ms
(3 rows)


2. Fast:
EXPLAIN ANALYZE SELECT p.firstname, p.lastname FROM origo_person p,=20
origo_person pcb WHERE pcb.onp_user_id =3D p.created_by limit 5;
                                                                           =
 =20
QUERY PLAN
=2D------------------------------------------------------------------------=
=2D---
 Limit  (cost=3D0.00..31526.55 rows=3D1 width=3D17) (actual time=3D0.096..0=
=2E170 rows=3D5=20
loops=3D1)
   ->  Merge Join  (cost=3D0.00..31526.55 rows=3D1 width=3D17) (actual=20
time=3D0.091..0.145 rows=3D5 loops=3D1)
         Merge Cond: (p.created_by =3D pcb.onp_user_id)
         ->  Index Scan using origo_person_createdby_idx on origo_person p =
=20
(cost=3D0.00..10697.70 rows=3D200827 width=3D21) (actual time=3D0.045..0.05=
7 rows=3D5=20
loops=3D1)
         ->  Index Scan using origo_person_onp_user_id_idx on origo_person =
pcb =20
(cost=3D0.00..19824.70 rows=3D200827 width=3D4) (actual time=3D0.032..0.044=
 rows=3D5=20
loops=3D1)
 Total runtime: 0.264 ms
(6 rows)


3. Slow:
EXPLAIN ANALYZE SELECT p.firstname, p.lastname FROM origo_person p,=20
origo_person pcb WHERE pcb.onp_user_id =3D p.created_by order by p.firstnam=
e=20
ASC limit 5;
                                                                           =
       =20
QUERY PLAN
=2D------------------------------------------------------------------------=
=2D---
 Limit  (cost=3D31526.56..31526.56 rows=3D1 width=3D17) (actual=20
time=3D2573.993..2574.015 rows=3D5 loops=3D1)
   ->  Sort  (cost=3D31526.56..31526.56 rows=3D1 width=3D17) (actual=20
time=3D2573.987..2573.994 rows=3D5 loops=3D1)
         Sort Key: p.firstname
         Sort Method:  top-N heapsort  Memory: 17kB
         ->  Merge Join  (cost=3D0.00..31526.55 rows=3D1 width=3D17) (actua=
l=20
time=3D0.098..2047.726 rows=3D200827 loops=3D1)
               Merge Cond: (p.created_by =3D pcb.onp_user_id)
               ->  Index Scan using origo_person_createdby_idx on origo_per=
son=20
p  (cost=3D0.00..10697.70 rows=3D200827 width=3D21) (actual time=3D0.052..4=
28.445=20
rows=3D200827 loops=3D1)
               ->  Index Scan using origo_person_onp_user_id_idx on=20
origo_person pcb  (cost=3D0.00..19824.70 rows=3D200827 width=3D4) (actual=20
time=3D0.031..424.250 rows=3D200828 loops=3D1)
 Total runtime: 2574.113 ms
(9 rows)

Can anybody point out to me why PG doesn't perform better on the last query?

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst All=C3=A9 11      | know how to do a thing and to watch         |
PO. Box 529 Sk=C3=B8yen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
=46ax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+