DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=Pl+rV01CVO8iPZtNPmQLO2pRYOu8J1zcYZQSXV0T4MWcTIBPrtY7ah484RHMA0lgjrRM8WqWy8A4GTL6ChrjNGyEKZ4E8mKZ4xSzYROzbSK/dsR9B/MYVcKzUjOdYIsh8VOXmz0hTT7+hbKF9gVMQi+XhdHq+Bt1KvnUqWsxtIo=
Message-ID: <4b09a0c050630084260f12728@mail.gmail.com>
Date: Thu, 30 Jun 2005 17:42:06 +0200
From: Jean-Max Reymond <jmreymond@gmail.com>
Reply-To: Jean-Max Reymond <jmreymond@gmail.com>
To: pgsql-performance@postgresql.org
Subject: start time very high
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

hi,

I have two computers, one laptop (1.5 GHz, 512 Mb RAM, 1 disk 4200)
and one big Sun (8Gb RAM, 2 disks SCSI).

On my laptop, I have this EXPLAIN ANALYZE

Sort  (cost=3D7.56..7.56 rows=3D1 width=3D28) (actual time=3D0.187..0.187=
=20
rows=3D0 loops=3D1)
   Sort Key: evolution, indx
   ->  Index Scan using index_xdb_child on xdb_child c1 =20
(cost=3D0.00..7.55 rows=3D1 width=3D28) (actual time=3D0.045..0.045 rows=3D=
0 loops=3D1)
         Index Cond: ((doc_id =3D 100) AND (ele_id =3D 1) AND (isremoved =
=3D 0))
         Filter: (evolution =3D (subplan))
         SubPlan
           ->  Aggregate  (cost=3D3.78..3.78 rows=3D1 width=3D4) (never exe=
cuted)
                 ->  Index Scan using index_xdb_child on xdb_child c2 =20
(cost=3D0.00..3.77 rows=3D1 width=3D4) (never executed)
                       Index Cond: ((doc_id =3D 100) AND (ele_id =3D 1))
                       Filter: ((evolution <=3D 0) AND (child_id =3D $0) AN=
D=20
(child_class =3D $1))
 Total runtime: 0.469 ms
(11 rows)


and on the SUN:

"Sort  (cost=3D7.56..7.56 rows=3D1 width=3D28) (actual time=3D26.335..26.33=
5
rows=3D0 loops=3D1)"
"  Sort Key: evolution, indx"
"  ->  Index Scan using index_xdb_child on xdb_child c1=20
(cost=3D0.00..7.55 rows=3D1 width=3D28) (actual time=3D26.121..26.121 rows=
=3D0
loops=3D1)"
"        Index Cond: ((doc_id =3D 100) AND (ele_id =3D 1) AND (isremoved =
=3D 0))"
"        Filter: (evolution =3D (subplan))"
"        SubPlan"
"          ->  Aggregate  (cost=3D3.78..3.78 rows=3D1 width=3D4) (never exe=
cuted)"
"                ->  Index Scan using index_xdb_child on xdb_child c2=20
(cost=3D0.00..3.77 rows=3D1 width=3D4) (never executed)"
"                      Index Cond: ((doc_id =3D 100) AND (ele_id =3D 1))"
"                      Filter: ((evolution <=3D 0) AND (child_id =3D $0)
AND (child_class =3D $1))"
"Total runtime: 26.646 ms"


so the request run in 26.646 ms on the Sun and 0.469ms on my laptop :-(=20
the database are the same, vacuumed and I think the Postgres (8.0.3)
are well configured.
The Sun has two disks and use the TABLESPACE to have index on one disk
and data's on the other disk.
It seems that the cost of the first sort is very high on the Sun.
How is it possible ?

the request:

explain analyze select * from XDB_CHILD c1
                                where c1.doc_id =3D 100
                                and c1.ele_id =3D 1
                                and c1.isremoved =3D 0
                                and c1.evolution =3D (select max(evolution)
                                                        from XDB_CHILD c2
                                                        where c2.doc_id=3D1=
00
                                                        and c2.ele_id=3D1
                                                        and c2.evolution<=
=3D0
                                                        and
c2.child_id=3Dc1.child_id
                                                        and
c2.child_class=3Dc1.child_class) ORDER BY c1.evolution, c1.indx

--=20
Jean-Max Reymond
CKR Solutions Open Source
Nice France
http://www.ckr-solutions.com