pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedstart time very high
4+ messages / 3 participants
[nested] [flat]
* start time very high
@ 2005-06-30 15:42 Jean-Max Reymond <jmreymond@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Jean-Max Reymond @ 2005-06-30 15:42 UTC (permalink / raw)
To: pgsql-performance
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=7.56..7.56 rows=1 width=28) (actual time=0.187..0.187
rows=0 loops=1)
Sort Key: evolution, indx
-> Index Scan using index_xdb_child on xdb_child c1
(cost=0.00..7.55 rows=1 width=28) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: ((doc_id = 100) AND (ele_id = 1) AND (isremoved = 0))
Filter: (evolution = (subplan))
SubPlan
-> Aggregate (cost=3.78..3.78 rows=1 width=4) (never executed)
-> Index Scan using index_xdb_child on xdb_child c2
(cost=0.00..3.77 rows=1 width=4) (never executed)
Index Cond: ((doc_id = 100) AND (ele_id = 1))
Filter: ((evolution <= 0) AND (child_id = $0) AND
(child_class = $1))
Total runtime: 0.469 ms
(11 rows)
and on the SUN:
"Sort (cost=7.56..7.56 rows=1 width=28) (actual time=26.335..26.335
rows=0 loops=1)"
" Sort Key: evolution, indx"
" -> Index Scan using index_xdb_child on xdb_child c1
(cost=0.00..7.55 rows=1 width=28) (actual time=26.121..26.121 rows=0
loops=1)"
" Index Cond: ((doc_id = 100) AND (ele_id = 1) AND (isremoved = 0))"
" Filter: (evolution = (subplan))"
" SubPlan"
" -> Aggregate (cost=3.78..3.78 rows=1 width=4) (never executed)"
" -> Index Scan using index_xdb_child on xdb_child c2
(cost=0.00..3.77 rows=1 width=4) (never executed)"
" Index Cond: ((doc_id = 100) AND (ele_id = 1))"
" Filter: ((evolution <= 0) AND (child_id = $0)
AND (child_class = $1))"
"Total runtime: 26.646 ms"
so the request run in 26.646 ms on the Sun and 0.469ms on my laptop :-(
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 = 100
and c1.ele_id = 1
and c1.isremoved = 0
and c1.evolution = (select max(evolution)
from XDB_CHILD c2
where c2.doc_id=100
and c2.ele_id=1
and c2.evolution<=0
and
c2.child_id=c1.child_id
and
c2.child_class=c1.child_class) ORDER BY c1.evolution, c1.indx
--
Jean-Max Reymond
CKR Solutions Open Source
Nice France
http://www.ckr-solutions.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: start time very high
@ 2005-06-30 16:23 Jean-Max Reymond <jmreymond@gmail.com>
parent: Jean-Max Reymond <jmreymond@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jean-Max Reymond @ 2005-06-30 16:23 UTC (permalink / raw)
To: pgsql-performance
2005/6/30, Jean-Max Reymond <jmreymond@gmail.com>:
> so the request run in 26.646 ms on the Sun and 0.469ms on my laptop :-(
> 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 ?
may be data's not loaded in memory but on disk ?
--
Jean-Max Reymond
CKR Solutions Open Source
Nice France
http://www.ckr-solutions.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: start time very high
@ 2005-06-30 19:01 Josh Berkus <josh@agliodbs.com>
parent: Jean-Max Reymond <jmreymond@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2005-06-30 19:01 UTC (permalink / raw)
To: Jean-Max Reymond <jmreymond@gmail.com>; +Cc: pgsql-performance
Jean-Max,
> I have two computers, one laptop (1.5 GHz, 512 Mb RAM, 1 disk 4200)
> and one big Sun (8Gb RAM, 2 disks SCSI).
Did you run each query several times? It looks like the index is cached
on one server and not on the other.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: start time very high
@ 2005-06-30 21:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jean-Max Reymond <jmreymond@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-06-30 21:01 UTC (permalink / raw)
To: Jean-Max Reymond <jmreymond@gmail.com>; +Cc: pgsql-performance
Jean-Max Reymond <jmreymond@gmail.com> writes:
> so the request run in 26.646 ms on the Sun and 0.469ms on my laptop :-(
> the database are the same, vacuumed and I think the Postgres (8.0.3)
> are well configured.
Are you sure they're both vacuumed? The Sun machine's behavior seems
consistent with the idea of a lot of dead rows in its copy of the table.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-30 21:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-30 15:42 start time very high Jean-Max Reymond <jmreymond@gmail.com>
2005-06-30 16:23 ` Jean-Max Reymond <jmreymond@gmail.com>
2005-06-30 19:01 ` Josh Berkus <josh@agliodbs.com>
2005-06-30 21:01 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox