speed Postgresql 7.0 vs PostgreSQL 6.5.2

From: Loïc TREGOUËT <loic(at)cri74(dot)org>
To: pgsql-admin(at)postgresql(dot)org
Subject: speed Postgresql 7.0 vs PostgreSQL 6.5.2
Date: 2000-09-18 10:30:14
Message-ID: 39C5EEB6.B1C18AAE@cri74.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hie ,

I've just install PostgreSQL 7.0.2 on i686-pc-linux-gnu (Pentium 233 with
64 Mo RAM)
But i've also PostgreSQL 6.5.2 "
" (P 450 with 256 Mo RAM)
I've dump one of my big database (500000 rows) on the new one .

I've made some test and one of my querie take 7 sec on the old server

1 sec on the new
The EXPLAIN seem to be different .
Is it a improvement or a mistake ?

With this good result , Postgresql 7.0 equals Mysql ! I'll be very happy , if
you confirm that PostgreSQL have such improve the speed ...

This is the request :

SELECT DISTINCT n.username from newlog2000 n , realms r where ( (n.username
like 'loic(at)%' ) and (r.host = 'toto.fr' ) and (
substr(n.username,strpos(n.username,'@')+1) = r.domain ) ) ;

NOTICE: QUERY PLAN: (on the new server PostgreSQL 7.0 200 Mhz 64 Mo RAM )

Unique (cost=253.85..254.07 rows=9 width=24)
-> Sort (cost=253.85..253.85 rows=91 width=24)
-> Nested Loop (cost=0.00..250.90 rows=91 width=24)
-> Seq Scan on realms r (cost=0.00..22.50 rows=10 width=12)
-> Index Scan using newuser2000 on newlog2000 n (cost=0.00..4.74
rows=905 width=12)

NOTICE: QUERY PLAN: (on the old server PostgreSQL 6.5 450 Mhz 256 Mo RAM)

Unique (cost=14107.55 rows=7897 width=24)
-> Sort (cost=14107.55 rows=7897 width=24)
-> Nested Loop (cost=14107.55 rows=7897 width=24)
-> Index Scan using newuser2000 on newlog2000 n (cost=9773.30
rows=658 width=12)
-> Seq Scan on realms r (cost=6.59 rows=120 width=12)

Thanks for all , and sorry for my english
....

loic

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Camm Maguire 2000-09-18 14:42:40 Re: backup large Pg databases with large objects
Previous Message Dronamraju Rajesh 2000-09-18 07:53:58 shmget fails for min buffers of postmaster also on solaris 7 .....