Re: [PERFORM] Sun vs a P2. Interesting results.

2003-08-26 Thread Neil Conway
On Tue, Aug 26, 2003 at 03:05:12PM -0400, Jeff wrote:
> On Tue, 26 Aug 2003, Darcy Buskermolen wrote:
> > I'm still seeing differences in the planner estimates, have you run a VACUUM
> > ANALYZE prior to running these tests?
> >
> I did. I shall retry that.. but the numbers (the cost estimates) are
> pretty close on both.  the actual times are very different.

I don't see why you need to bother, the query plans & cost estimates
are similar enough I doubt that's the problem.
 
> As I said in my first email IO isn't the problem here - the data set is
> small enough that it is all cached (~10MB).  iostat reports 0 activity on
> the disks on both the sun and p2.

Would it be possible to get a profile (e.g. gprof output) for a postgres
backend executing the query on the Sun machine?

-Neil


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Sun vs a P2. Interesting results.

2003-08-26 Thread Jeff
On Tue, 26 Aug 2003, Darcy Buskermolen wrote:

> I'm still seeing differences in the planner estimates, have you run a VACUUM
> ANALYZE prior to running these tests?
>
I did. I shall retry that.. but the numbers (the cost estimates) are
pretty close on both.  the actual times are very different.

> Also, are the disk subsystems in these 2 systems the same? You may be seeing
> some discrepancies in things spindle speed,  U160 vs U320, throughput on
> specific RAID controlers, different blocksize, ect.
>

As I said in my first email IO isn't the problem here - the data set is
small enough that it is all cached (~10MB).  iostat reports 0 activity on
the disks on both the sun and p2.

and I just ran teh test again with 40 clients: 730s for hte p2, 1100 for
the sun.  (0% idle on both of them, no IO).  I think the next I may try is
recompiling with a newer gcc.


--
Jeff Trout <[EMAIL PROTECTED]>
http://www.jefftrout.com/
http://www.stuarthamm.net/



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Sun vs a P2. Interesting results.

2003-08-26 Thread Darcy Buskermolen
I'm still seeing differences in the planner estimates, have you run a VACUUM 
ANALYZE prior to running these tests?

Also, are the disk subsystems in these 2 systems the same? You may be seeing 
some discrepancies in things spindle speed,  U160 vs U320, throughput on 
specific RAID controlers, different blocksize, ect.


On Tuesday 26 August 2003 11:41, Jeff wrote:
> On Tue, 26 Aug 2003, Darcy Buskermolen wrote:
> > Also, after having taken another look at this, you aren't preforming the
> > same query on both datasets, so you can't expect them to generate the
> > same results, or the same query plans, or even comparable times. Please
> > retry your tests with identical queries , specify the dates, don;t use a
> > function like now() to retrieve them.
>
> Given what you said in the previous email and this one here's some new
> information.  I redid the query to use a static starting time and I ran
> 19 beaters in parallel.  After I send this mail out I'll try it with 40.
>
> New Query:
>
> select userkey, dob, email, gender, country from userprofile
> where  gender_v  and gender='m'
>  and  country_v and country = 'br'
>  and dob_v
>  and dob  >= '2003-08-26'::timestamptz - '29
> years'::interval
>  and dob <= '2003-08-26'::timestamptz - '18 years'::interval
> order by dob asc
> limit 20
> offset 100
>
> Explain Analyze's: (basically the same)
> Sun:
>  Limit  (cost=2390.05..2390.10 rows=20 width=67) (actual
> time=1098.34..1098.39 rows=20 loops=1)
>->  Sort  (cost=2389.80..2390.24 rows=178 width=67) (actual
> time=1098.16..1098.28 rows=121 loops=1)
>  Sort Key: dob
>  ->  Seq Scan on imuserprofile  (cost=0.00..2383.14 rows=178
> width=67) (actual time=0.38..1068.94 rows=1783 loops=1)
>Filter: (gender_v AND (gender = 'm'::character varying) AND
> country_v AND (country = 'br'::character varying) AND dob_v AND (dob >=
> '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <=
> '1985-08-26 00:00:00-04'::timestamp with time zone))
>  Total runtime: 1099.93 msec
> (6 rows)
>
>
> p2
>
>  Limit  (cost=2353.38..2353.43 rows=20 width=67) (actual
> time=371.75..371.83 rows=20 loops=1)
>->  Sort  (cost=2353.13..2353.60 rows=186 width=67) (actual
> time=371.46..371.63 rows=121 loops=1)
>  Sort Key: dob
>  ->  Seq Scan on imuserprofile  (cost=0.00..2346.14 rows=186
> width=67) (actual time=0.17..345.53 rows=1783 loops=1)
>Filter: (gender_v AND (gender = 'm'::character varying) AND
> country_v AND (country = 'br'::character varying) AND dob_v AND (dob >=
> '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <=
> '1985-08-26 00:00:00-04'::timestamp with time zone))
>  Total runtime: 372.63 msec
> (6 rows)
>
>
> I ran this query 100 times per beater (no prepared queries) and ran
> 19 beaters in parellel.
>
> P2 Machine: 345sec avg
> Sun:565sec avg
>
>
>
> I know solaris/sun isn't the preferred pg platform, and we have plenty of
> capicty even with these numbers, I just find it a little suprising the
> speed difference.

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PERFORM] Sun vs a P2. Interesting results.

2003-08-26 Thread Jeff
On Tue, 26 Aug 2003, Darcy Buskermolen wrote:

> Also, after having taken another look at this, you aren't preforming the same
> query on both datasets, so you can't expect them to generate the same
> results, or the same query plans, or even comparable times. Please retry your
> tests with identical queries , specify the dates, don;t use a function like
> now() to retrieve them.
>

Given what you said in the previous email and this one here's some new
information.  I redid the query to use a static starting time and I ran
19 beaters in parallel.  After I send this mail out I'll try it with 40.

New Query:

select userkey, dob, email, gender, country from userprofile
where  gender_v  and gender='m'
   and  country_v and country = 'br'
   and dob_v
   and dob  >= '2003-08-26'::timestamptz - '29
years'::interval
   and dob <= '2003-08-26'::timestamptz - '18 years'::interval
order by dob asc
limit 20
offset 100

Explain Analyze's: (basically the same)
Sun:
 Limit  (cost=2390.05..2390.10 rows=20 width=67) (actual
time=1098.34..1098.39 rows=20 loops=1)
   ->  Sort  (cost=2389.80..2390.24 rows=178 width=67) (actual
time=1098.16..1098.28 rows=121 loops=1)
 Sort Key: dob
 ->  Seq Scan on imuserprofile  (cost=0.00..2383.14 rows=178
width=67) (actual time=0.38..1068.94 rows=1783 loops=1)
   Filter: (gender_v AND (gender = 'm'::character varying) AND
country_v AND (country = 'br'::character varying) AND dob_v AND (dob >=
'1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <=
'1985-08-26 00:00:00-04'::timestamp with time zone))
 Total runtime: 1099.93 msec
(6 rows)


p2

 Limit  (cost=2353.38..2353.43 rows=20 width=67) (actual
time=371.75..371.83 rows=20 loops=1)
   ->  Sort  (cost=2353.13..2353.60 rows=186 width=67) (actual
time=371.46..371.63 rows=121 loops=1)
 Sort Key: dob
 ->  Seq Scan on imuserprofile  (cost=0.00..2346.14 rows=186
width=67) (actual time=0.17..345.53 rows=1783 loops=1)
   Filter: (gender_v AND (gender = 'm'::character varying) AND
country_v AND (country = 'br'::character varying) AND dob_v AND (dob >=
'1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <=
'1985-08-26 00:00:00-04'::timestamp with time zone))
 Total runtime: 372.63 msec
(6 rows)


I ran this query 100 times per beater (no prepared queries) and ran
19 beaters in parellel.

P2 Machine: 345sec avg
Sun:565sec avg



I know solaris/sun isn't the preferred pg platform, and we have plenty of
capicty even with these numbers, I just find it a little suprising the
speed difference.

--
Jeff Trout <[EMAIL PROTECTED]>
http://www.jefftrout.com/
http://www.stuarthamm.net/



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [PERFORM] What is the fastest way to get a resultset

2003-08-26 Thread Neil Conway
On Tue, Aug 26, 2003 at 02:18:23AM -0700, Bupp Phillips wrote:
> It there a way to get server side cursors with Postgresql like SQLServer has
> or is this a limitation that it has?

http://www.postgresql.org/docs/7.3/static/sql-declare.html
http://www.postgresql.org/docs/7.3/static/sql-fetch.html

-Neil


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [PERFORM] Sun vs a P2. Interesting results.

2003-08-26 Thread Darcy Buskermolen
Also, after having taken another look at this, you aren't preforming the same 
query on both datasets, so you can't expect them to generate the same 
results, or the same query plans, or even comparable times. Please retry your 
tests with identical queries , specify the dates, don;t use a function like 
now() to retrieve them.


On Tuesday 26 August 2003 05:34, Jeff wrote:
> Here's an interesting situation, and I think it may be just that Sun
> stinks.
>
> I was recently given the go ahead to switch from Informix to Postgres on
> one of our properties.  (I had dozens of performance comparisons showing
> how slow Informix was compared to it and my boss seeing me struggle trying
> to make it run fast while Postgres, nearly out of the box, was simply
> spanking it.).
>
>
> Well, in order to facilitate things we were going to run pg on a 4 cpu
> (ultrasparc ii 400Mhz) sun with 4gb of memory (also the current informix
> box. It isn't very loaded). Now I know FreeBSD/Linux is preferred (and
> where I do a lot of development and testing).   But check this out for
> interesting results.
>
> The Hardware:
> Machine A:  4 CPU Sun Ultrasparc II 400Mhz, 4GB mem, 20GB RAID5, Solaris 8
> (32 bit mode)
>
> Machine B:  2 CPU Pentium II, 450Mhz, 512MB mem, 18GB RAID0 (2 old scsi
> disks) Linux 2.4.18 (Stock redhat 8.0)
>
> The software: PG 7.3.4 compiled myself.  (Reading specs from
> /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.9/2.95.3/specs gcc version 2.95.3
> 20010315 (release) (The solaris 8 box has no compilers, could this be the
> issue?)  and (Reading specs from
> /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
> Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
> --infodir=/u
> sr/share/info --enable-shared --enable-threads=posix --disable-checking
> --host=i
> 386-redhat-linux --with-system-zlib --enable-__cxa_atexit
> Thread model: posix
> gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7))
>
> Ok. Maybe the compiler (I'll try installing a newer gcc for sun later
> today).
>
> The PG.conf:
> shared_buffers = 2000
> sort_mem = 8192
> effective_cache_size = 32000
> tcpip_sockets = true
>
> The Schema:
>
> userprofile:
>
> userkey   | character varying(128)   |
>  displayname   | character varying(128)   |
>  displayname_v | boolean  | default 'f'
>  name  | character varying(128)   |
>  name_v| boolean  | default 'f'
>  email | character varying(128)   |
>  email_v   | boolean  | default 'f'
>  gender| character varying(1) |
>  gender_v  | boolean  | default 'f'
>  country   | character varying(64)|
>  country_v | boolean  | default 'f'
>  zip   | character varying(10)|
>  zip_v | boolean  | default 'f'
>  city  | character varying(128)   |
>  city_v| boolean  | default 'f'
>  state | character varying(10)|
>  state_v   | boolean  | default 'f'
>  lang  | character varying(2) |
>  lang_v| boolean  | default 'f'
>  url   | character varying(255)   |
> url_v | boolean  | default 'f'
>  phone | character varying(64)|
>  phone_v   | boolean  | default 'f'
>  phonemobile   | character varying(64)|
>  phonemobile_v | boolean  | default 'f'
>  phonefax  | character varying(64)|
>  phonefax_v| boolean  | default 'f'
>  dob   | timestamp with time zone |
>  dob_v | boolean  | default 'f'
>  interests_v   | boolean  | default 'f'
>  description   | character varying(255)   |
>  description2  | character varying(255)   |
>  description_v | boolean  | default 'f'
>
> (Yes, I kknow it isn't good - a lot of it is because it is the same schema
> I had to use on informix. Convienantly you cannot do much with a textblob
> on infomrix, so I have to use big varchar's, but that is a fiffernt
> story).
>
> The magic query:
>
> select userkey, dob, email, gender, country from imuserprofile
> where  gender_v  and gender='m'
> and  country_v and country = 'br'
> and dob_v = 't'
> and dob >= 'now'::timestamptz - '29 years'::interval
> and dob <= 'now'::timestamptz - '18 years'::interval
> order by dob asc
> limit 20
> offset 100
>
> (Page 5 of male brazillians, 18-29)
>
> Now the P2 runs this in about 0.3 seconds, and hte sun box runs it in 1
> second.
> Here's the explain analyze's on each:
>
> P2:
> Limit  (cost=2484.52..2484.57 rows=20 width=67) (actual
> time=377.32..377.41 row
> s=20 loops=1)
>->  Sort  (cost=2484.27..2484.74 rows=186 width=67) (actual
> time=377.02..377.
> 21 rows=121 loops=1)
>  Sort Key: dob
>  ->  Seq Scan on userprofile  (cost=0.00..2477.28 rows=186
> width=67) (
> actual time=0.15..350.93 rows=1783 loops=1)
>Filter: (gen

Re: [PERFORM] What is the fastest way to get a resultset

2003-08-26 Thread Bupp Phillips
Is this something that can be done thru a SQL statement, or are you saying
that I need to develop logic to handle this because the database won't hold
the resultset on the server, but instead sends it all to the client?

It there a way to get server side cursors with Postgresql like SQLServer has
or is this a limitation that it has?

Thanks

"Jeff" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Mon, 25 Aug 2003, Bupp Phillips wrote:
>
> >
> > I have a table that has 103,000 records in it (record size is about 953
> > bytes) and when I do a select all (select * from ) it takes a
> > whopping 30 secs for the data to return!!
> >
> > SQLServer on the other hand takes 6 secs, but you can also use what is
> > called a firehose cursor, which will return the data in < 1 sec.
> >
> You probably want a cursor.
> Typically what happens is postgres sends _all_ the data to the client -
> which can be rather substantial.  A cursor allows you to say "get me the
> first 1000 records. now the next 1000" - it should get you the speed you
> want.
>
>
> --
> Jeff Trout <[EMAIL PROTECTED]>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Sun vs a P2. Interesting results.

2003-08-26 Thread Darcy Buskermolen
I spoke with my SUN admin, and this is what he had to say about what you are 
seeing.

Sun gear is known to show a lower than Intel performance on light loads, rerun 
your test with 100 concurrent users (queries) and see what happens.  Also he 
recommends installing a 64bit version of Solaris, the 32bit robs a lot of 
performance as well.


On Tuesday 26 August 2003 05:34, Jeff wrote:
> Here's an interesting situation, and I think it may be just that Sun
> stinks.
>
> I was recently given the go ahead to switch from Informix to Postgres on
> one of our properties.  (I had dozens of performance comparisons showing
> how slow Informix was compared to it and my boss seeing me struggle trying
> to make it run fast while Postgres, nearly out of the box, was simply
> spanking it.).
>
>
> Well, in order to facilitate things we were going to run pg on a 4 cpu
> (ultrasparc ii 400Mhz) sun with 4gb of memory (also the current informix
> box. It isn't very loaded). Now I know FreeBSD/Linux is preferred (and
> where I do a lot of development and testing).   But check this out for
> interesting results.
>
> The Hardware:
> Machine A:  4 CPU Sun Ultrasparc II 400Mhz, 4GB mem, 20GB RAID5, Solaris 8
> (32 bit mode)
>
> Machine B:  2 CPU Pentium II, 450Mhz, 512MB mem, 18GB RAID0 (2 old scsi
> disks) Linux 2.4.18 (Stock redhat 8.0)
>
> The software: PG 7.3.4 compiled myself.  (Reading specs from
> /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.9/2.95.3/specs gcc version 2.95.3
> 20010315 (release) (The solaris 8 box has no compilers, could this be the
> issue?)  and (Reading specs from
> /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
> Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
> --infodir=/u
> sr/share/info --enable-shared --enable-threads=posix --disable-checking
> --host=i
> 386-redhat-linux --with-system-zlib --enable-__cxa_atexit
> Thread model: posix
> gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7))
>
> Ok. Maybe the compiler (I'll try installing a newer gcc for sun later
> today).
>
> The PG.conf:
> shared_buffers = 2000
> sort_mem = 8192
> effective_cache_size = 32000
> tcpip_sockets = true
>
> The Schema:
>
> userprofile:
>
> userkey   | character varying(128)   |
>  displayname   | character varying(128)   |
>  displayname_v | boolean  | default 'f'
>  name  | character varying(128)   |
>  name_v| boolean  | default 'f'
>  email | character varying(128)   |
>  email_v   | boolean  | default 'f'
>  gender| character varying(1) |
>  gender_v  | boolean  | default 'f'
>  country   | character varying(64)|
>  country_v | boolean  | default 'f'
>  zip   | character varying(10)|
>  zip_v | boolean  | default 'f'
>  city  | character varying(128)   |
>  city_v| boolean  | default 'f'
>  state | character varying(10)|
>  state_v   | boolean  | default 'f'
>  lang  | character varying(2) |
>  lang_v| boolean  | default 'f'
>  url   | character varying(255)   |
> url_v | boolean  | default 'f'
>  phone | character varying(64)|
>  phone_v   | boolean  | default 'f'
>  phonemobile   | character varying(64)|
>  phonemobile_v | boolean  | default 'f'
>  phonefax  | character varying(64)|
>  phonefax_v| boolean  | default 'f'
>  dob   | timestamp with time zone |
>  dob_v | boolean  | default 'f'
>  interests_v   | boolean  | default 'f'
>  description   | character varying(255)   |
>  description2  | character varying(255)   |
>  description_v | boolean  | default 'f'
>
> (Yes, I kknow it isn't good - a lot of it is because it is the same schema
> I had to use on informix. Convienantly you cannot do much with a textblob
> on infomrix, so I have to use big varchar's, but that is a fiffernt
> story).
>
> The magic query:
>
> select userkey, dob, email, gender, country from imuserprofile
> where  gender_v  and gender='m'
> and  country_v and country = 'br'
> and dob_v = 't'
> and dob >= 'now'::timestamptz - '29 years'::interval
> and dob <= 'now'::timestamptz - '18 years'::interval
> order by dob asc
> limit 20
> offset 100
>
> (Page 5 of male brazillians, 18-29)
>
> Now the P2 runs this in about 0.3 seconds, and hte sun box runs it in 1
> second.
> Here's the explain analyze's on each:
>
> P2:
> Limit  (cost=2484.52..2484.57 rows=20 width=67) (actual
> time=377.32..377.41 row
> s=20 loops=1)
>->  Sort  (cost=2484.27..2484.74 rows=186 width=67) (actual
> time=377.02..377.
> 21 rows=121 loops=1)
>  Sort Key: dob
>  ->  Seq Scan on userprofile  (cost=0.00..2477.28 rows=186
> width=67) (
> actual time=0.15..350.93 rows=1783 loops=1)
>F

Re: [PERFORM] Best tweak for fast results.. ?

2003-08-26 Thread Richard Huxton
On Tuesday 26 August 2003 14:42, JM wrote:
> need input on parameter values on confs...
>
> our database is getting 1000 transactions/sec on peak periods..
>
> sitting on RH 7.3
> 2.4.7-10smp
> RAM: 1028400
> SWAP: 2040244
>
> queries are just simple select statements based on timestamps, varchars...
> less on joins... on a 300K rows..

Assuming you're getting good query plans (check the output of EXPLAIN 
ANALYSE)...

Start by checking the output of vmstat/iostat during busy periods - this will 
tell you whether CPU/IO/RAM is the bottleneck.

There is a good starter for tuning PG at:
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

Assuming your rows aren't too wide, they're probably mostly cached by Linux, 
so you probably don't want to overdo the shared buffers/sort memory and make 
sure the effective cache size is accurate.
-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


[PERFORM] Sun vs a P2. Interesting results.

2003-08-26 Thread Jeff
Here's an interesting situation, and I think it may be just that Sun
stinks.

I was recently given the go ahead to switch from Informix to Postgres on
one of our properties.  (I had dozens of performance comparisons showing
how slow Informix was compared to it and my boss seeing me struggle trying
to make it run fast while Postgres, nearly out of the box, was simply
spanking it.).


Well, in order to facilitate things we were going to run pg on a 4 cpu
(ultrasparc ii 400Mhz) sun with 4gb of memory (also the current informix
box. It isn't very loaded). Now I know FreeBSD/Linux is preferred (and
where I do a lot of development and testing).   But check this out for
interesting results.

The Hardware:
Machine A:  4 CPU Sun Ultrasparc II 400Mhz, 4GB mem, 20GB RAID5, Solaris 8
(32 bit mode)

Machine B:  2 CPU Pentium II, 450Mhz, 512MB mem, 18GB RAID0 (2 old scsi
disks) Linux 2.4.18 (Stock redhat 8.0)

The software: PG 7.3.4 compiled myself.  (Reading specs from
/opt/sfw/lib/gcc-lib/sparc-sun-solaris2.9/2.95.3/specs gcc version 2.95.3
20010315 (release) (The solaris 8 box has no compilers, could this be the
issue?)  and (Reading specs from
/usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/u
sr/share/info --enable-shared --enable-threads=posix --disable-checking
--host=i
386-redhat-linux --with-system-zlib --enable-__cxa_atexit
Thread model: posix
gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7))

Ok. Maybe the compiler (I'll try installing a newer gcc for sun later
today).

The PG.conf:
shared_buffers = 2000
sort_mem = 8192
effective_cache_size = 32000
tcpip_sockets = true

The Schema:

userprofile:

userkey   | character varying(128)   |
 displayname   | character varying(128)   |
 displayname_v | boolean  | default 'f'
 name  | character varying(128)   |
 name_v| boolean  | default 'f'
 email | character varying(128)   |
 email_v   | boolean  | default 'f'
 gender| character varying(1) |
 gender_v  | boolean  | default 'f'
 country   | character varying(64)|
 country_v | boolean  | default 'f'
 zip   | character varying(10)|
 zip_v | boolean  | default 'f'
 city  | character varying(128)   |
 city_v| boolean  | default 'f'
 state | character varying(10)|
 state_v   | boolean  | default 'f'
 lang  | character varying(2) |
 lang_v| boolean  | default 'f'
 url   | character varying(255)   |
url_v | boolean  | default 'f'
 phone | character varying(64)|
 phone_v   | boolean  | default 'f'
 phonemobile   | character varying(64)|
 phonemobile_v | boolean  | default 'f'
 phonefax  | character varying(64)|
 phonefax_v| boolean  | default 'f'
 dob   | timestamp with time zone |
 dob_v | boolean  | default 'f'
 interests_v   | boolean  | default 'f'
 description   | character varying(255)   |
 description2  | character varying(255)   |
 description_v | boolean  | default 'f'

(Yes, I kknow it isn't good - a lot of it is because it is the same schema
I had to use on informix. Convienantly you cannot do much with a textblob
on infomrix, so I have to use big varchar's, but that is a fiffernt
story).

The magic query:

select userkey, dob, email, gender, country from imuserprofile
where  gender_v  and gender='m'
and  country_v and country = 'br'
and dob_v = 't'
and dob >= 'now'::timestamptz - '29 years'::interval
and dob <= 'now'::timestamptz - '18 years'::interval
order by dob asc
limit 20
offset 100

(Page 5 of male brazillians, 18-29)

Now the P2 runs this in about 0.3 seconds, and hte sun box runs it in 1
second.
Here's the explain analyze's on each:

P2:
Limit  (cost=2484.52..2484.57 rows=20 width=67) (actual
time=377.32..377.41 row
s=20 loops=1)
   ->  Sort  (cost=2484.27..2484.74 rows=186 width=67) (actual
time=377.02..377.
21 rows=121 loops=1)
 Sort Key: dob
 ->  Seq Scan on userprofile  (cost=0.00..2477.28 rows=186
width=67) (
actual time=0.15..350.93 rows=1783 loops=1)
   Filter: (gender_v AND (gender = 'm'::character varying) AND
count
ry_v AND (country = 'br'::character varying) AND (dob_v = true) AND (dob
>= '197
4-08-26 07:13:15.903437-04'::timestamp with time zone) AND (dob <=
'1985-08-26 0
7:13:15.903437-04'::timestamp with time zone))
 Total runtime: 378.21 msec
(6 rows)

Sun:
Limit  (cost=2521.19..2521.24 rows=20 width=67) (actual
time=1041.14..1041.20 r
ows=20 loops=1)
   ->  Sort  (cost=2520.94..2521.39 rows=178 width=67) (actual
time=1040.96..104
1.08 rows=121 loops=1)
 Sort Key: dob
 ->  Seq Scan on userprofile  (cost=0.00..2514.28 rows=178

[PERFORM] Best tweak for fast results.. ?

2003-08-26 Thread JM
need input on parameter values on confs...

our database is getting 1000 transactions/sec on peak periods..

sitting on RH 7.3 
2.4.7-10smp
RAM: 1028400
SWAP: 2040244

queries are just simple select statements based on timestamps, varchars...
less on joins... on a 300K rows..


TIA

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Query too slow

2003-08-26 Thread Tom Lane
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Tue, 26 Aug 2003, Ang Chin Han wrote:
>> Veering aside a bit, since we usually pinpoint performance problems by
>> looking at EXPLAIN ANALYZE's differences between the planner's
>> estimation and actual execution's stats, what's involved in parsing the
>> EXPLAIN ANALYZE results, and highlighting the places where they are way
>> different? Bold, underline, or put some asterisks in front of those steps.

> The hardest part is determining where it matters I think.  You can use the
> row counts as the base for that, but going from 1 row to 50 is not
> necessarily going to be an issue, but it might be if a nested loop is
> chosen.

We've been chatting about this idea among the Red Hat group.  The RHDB
Visual Explain tool (get it at http://sources.redhat.com/rhdb/) already
computes the percent of total runtime represented by each plan node.
It seems like we could highlight nodes based on a large difference
between estimated and actual percentage, or just highlight the nodes
that are more than X percent of the runtime.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Query too slow

2003-08-26 Thread Stephan Szabo
On Tue, 26 Aug 2003, Ang Chin Han wrote:

> Stephan Szabo wrote:
>
> > Looking at the explain:
>
> Veering aside a bit, since we usually pinpoint performance problems by
> looking at EXPLAIN ANALYZE's differences between the planner's
> estimation and actual execution's stats, what's involved in parsing the
> EXPLAIN ANALYZE results, and highlighting the places where they are way
> different? Bold, underline, or put some asterisks in front of those steps.

The hardest part is determining where it matters I think.  You can use the
row counts as the base for that, but going from 1 row to 50 is not
necessarily going to be an issue, but it might be if a nested loop is
chosen.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Query too slow

2003-08-26 Thread Ang Chin Han
Stephan Szabo wrote:

Looking at the explain:
Veering aside a bit, since we usually pinpoint performance problems by 
looking at EXPLAIN ANALYZE's differences between the planner's 
estimation and actual execution's stats, what's involved in parsing the 
EXPLAIN ANALYZE results, and highlighting the places where they are way 
different? Bold, underline, or put some asterisks in front of those steps.

Makes looking at big EXPLAIN ANALYZE trees much easier.

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 
GNU/Linux
  2:30pm  up 243 days,  5:48,  8 users,  load average: 5.52, 5.29, 5.10


pgp0.pgp
Description: PGP signature


Re: [PERFORM] What is the fastest way to get a resultset

2003-08-26 Thread Magnus Naeslund(f)
Bupp Phillips <[EMAIL PROTECTED]> wrote:
> I'm very new to Postgresql, so don't beat me up to bad if you see a
> problem, just inform me what I've done wrong.
>
> I'm use Postgresql 7.2 (PeerDirect's Windows port) on Win2000 384MB
> RAM 10GB of Free space 800 Mhz, using the ODBC driver 7.03.01.00.
>
> I have a table that has 103,000 records in it (record size is about
> 953 bytes) and when I do a select all (select * from ) it
> takes a whopping 30 secs for the data to return!!
>
> SQLServer on the other hand takes 6 secs, but you can also use what is
> called a firehose cursor, which will return the data in < 1 sec.
>
> I have done everything that I know how to speed this up, does anyone
> have any advise?
>

Probably you need to fetch more than one row at a time.
I made that misstake once myself :)

Magnus


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [PERFORM] What is the fastest way to get a resultset

2003-08-26 Thread Jeff
On Mon, 25 Aug 2003, Bupp Phillips wrote:

>
> I have a table that has 103,000 records in it (record size is about 953
> bytes) and when I do a select all (select * from ) it takes a
> whopping 30 secs for the data to return!!
>
> SQLServer on the other hand takes 6 secs, but you can also use what is
> called a firehose cursor, which will return the data in < 1 sec.
>
You probably want a cursor.
Typically what happens is postgres sends _all_ the data to the client -
which can be rather substantial.  A cursor allows you to say "get me the
first 1000 records. now the next 1000" - it should get you the speed you
want.


--
Jeff Trout <[EMAIL PROTECTED]>
http://www.jefftrout.com/
http://www.stuarthamm.net/



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Replication Ideas

2003-08-26 Thread Christopher Browne
A long time ago, in a galaxy far, far away, "Bupp Phillips" <[EMAIL PROTECTED]> wrote:
>I have a table that has 103,000 records in it (record size is about
>953 bytes) and when I do a select all (select * from ) it takes
>a whopping 30 secs for the data to return!!
 
>SQLServer on the other hand takes 6 secs, but you can also use what is
>called a firehose cursor, which will return the data in < 1 sec. 
 
>I have done everything that I know how to speed this up, does anyone
>have any advise?

Have you VACUUMed the table?  30 seconds to start getting data back
from such a query _seems_ a liittle high.

It would be quite a reasonable idea to open up a CURSOR and request
the data in more byte-sized pieces so that the result set wouldn't
forcibly bloat in any one spot.

You start by submitting the cursor definition, inside a transaction:
  begin transaction;
  declare cursor my_fire_hose for select * from ;

You then iterate over the following, which fetches 1000 rows at a time:
  fetch forward 1000 in my_fire_hose;

That should cut down the time it takes to start getting records to
near enough to zero...
-- 
output = reverse("gro.gultn" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/lisp.html
"Microsoft is sort of a mixture between the Borg and the
Ferengi. Combine the Borg marketing with Ferengi networking..."
-- Andre Beck in dcouln

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])