Hi,
Many thanks for all your thoughts and advice. With just 2GB or RAM, no
change to the harddisc (still SATA) but proper tuning of Postgresql
(still 7.4) and aggressive normalization to shrink row width, I have
managed to get suitable performance, with, when fully cached, queries on
a 5 million r
Mark Kirkwood wrote:
Matthew Sackman wrote:
I need to get to the stage where I can run queries such as:
>
select street, locality_1, locality_2, city from address where (city =
'Nottingham' or locality_2 = 'Nottingham'
or locality_1 = 'Nottingham')
and upper(substring(street from
Akshay Mathur wrote:
Ron,
Can you give me some pointers to make the tables RAM resident. If one
does so, is the program accessing the data need to change. Does pgsql
take care to write the data to disk?
PostgreSQL tried to intelligently cache information and then will also
use the OS disk
Matthew,
> Well, this is a development box. But the live box wouldn't be much more
> than RAID 1 on SCSI 10ks so that should only be a halving of seek time,
> not the 1000 times reduction I'm after!
If you're looking for 1000 times reduction, I think you're going to need
*considerably* beefier h
Networks, Inc. (www.airtightnetworks.net)
O: +91 20 2588 1555 ext 205
F: +91 20 2588 1445
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ron
Sent: Friday, September 02, 2005 2:36 AM
To: Tom Lane; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Massive
Matthew Sackman wrote:
I need to get to the stage where I can run queries such as:
>
select street, locality_1, locality_2, city from address
where (city = 'Nottingham' or locality_2 = 'Nottingham'
or locality_1 = 'Nottingham')
and upper(substring(street from 1 for 1)) = 'A'
group b
It would be good to see EXPLAIN ANALYZE output for the three queries
below (the real vs. estimated row counts being of interest).
The number of pages in your address table might be interesting to know too.
regards
Mark
Matthew Sackman wrote (with a fair bit of snippage):
explain select local
On Thu, Sep 01, 2005 at 06:42:31PM +0100, Matthew Sackman wrote:
> flat_extra | character varying(100) | not null
> number | character varying(100) | not null
> street | character varying(100) | not null
> locality_1 | character varying(100) | not
At 06:22 PM 9/1/2005, Matthew Sackman wrote:
On Thu, Sep 01, 2005 at 06:05:43PM -0400, Ron wrote:
>
> Since I assume you are not going to run anything with the string
> "unstable" in its name in production (?!), why not try a decent
> production ready distro like SUSE 9.x and see how pg 8.0.3 run
On Thu, Sep 01, 2005 at 06:42:31PM +0100, Matthew Sackman wrote:
>
> "address_pc_top_index" btree (postcode_top)
> "address_pc_top_middle_bottom_index" btree (postcode_top,
> postcode_middle, postcode_bottom)
> "address_pc_top_middle_index" btree (postcode_t
On Thu, Sep 01, 2005 at 06:05:43PM -0400, Ron wrote:
> > Selection from the database is, hence the indexes.
>
> A DB _without_ indexes that fits into RAM during ordinary operation
> may actually be faster than a DB _with_ indexes that does
> not. Fitting the entire DB into RAM during ordinary o
On Thu, Sep 01, 2005 at 11:52:45PM +0200, Steinar H. Gunderson wrote:
> On Thu, Sep 01, 2005 at 10:13:59PM +0100, Matthew Sackman wrote:
> > Well that's the thing - on the queries where it decides to use the index
> > it only reads at around 3MB/s and the CPU is maxed out, whereas when it
> > doesn
At 05:06 PM 9/1/2005, Matthew Sackman wrote:
On Thu, Sep 01, 2005 at 10:09:30PM +0200, Steinar H. Gunderson wrote:
> > "address_city_index" btree (city)
> > "address_county_index" btree (county)
> > "address_locality_1_index" btree (locality_1)
> > "address_locality_2_index" btree
> -Original Message-
> From: Alvaro Herrera [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 01, 2005 3:34 PM
> To: Merlin Moncure
> Cc: Matthew Sackman; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Massive performance issues
>
> On Thu, Sep 01,
On Thu, Sep 01, 2005 at 02:26:47PM -0700, Jeff Frost wrote:
> >Well I've got 1GB of RAM, but from analysis of its use, a fair amount
> >isn't being used. About 50% is actually in use by applications and about
> >half of the rest is cache and the rest isn't being used. Has this to do
> >with the max
On Thu, Sep 01, 2005 at 10:13:59PM +0100, Matthew Sackman wrote:
> Well that's the thing - on the queries where it decides to use the index
> it only reads at around 3MB/s and the CPU is maxed out, whereas when it
> doesn't use the index, the disk is being read at 60MB/s. So when it
> decides to us
Well I've got 1GB of RAM, but from analysis of its use, a fair amount
isn't being used. About 50% is actually in use by applications and about
half of the rest is cache and the rest isn't being used. Has this to do
with the max_fsm_pages and max_fsm_relations settings? I've pretty much
not touched
On Thu, Sep 01, 2005 at 10:54:45PM +0200, Arjen van der Meijden wrote:
> On 1-9-2005 19:42, Matthew Sackman wrote:
> >Obviously, to me, this is a problem, I need these queries to be under a
> >second to complete. Is this unreasonable? What can I do to make this "go
> >faster"? I've considered norma
On Thu, Sep 01, 2005 at 10:09:30PM +0200, Steinar H. Gunderson wrote:
> > "address_city_index" btree (city)
> > "address_county_index" btree (county)
> > "address_locality_1_index" btree (locality_1)
> > "address_locality_2_index" btree (locality_2)
> > "address_pc_bottom_index"
At 04:25 PM 9/1/2005, Tom Lane wrote:
Ron <[EMAIL PROTECTED]> writes:
> ... Your target is to have each row take <= 512B.
Ron, are you assuming that the varchar fields are blank-padded or
something? I think it's highly unlikely that he's got more than a
couple hundred bytes per row right now
This should be able to run _very_ fast.
At 01:42 PM 9/1/2005, Matthew Sackman wrote:
Hi,
I'm having performance issues with a table consisting of 2,043,133 rows. The
schema is:
\d address
Table "public.address"
Column| Type | Modifiers
--
Ron <[EMAIL PROTECTED]> writes:
> ... Your target is to have each row take <= 512B.
Ron, are you assuming that the varchar fields are blank-padded or
something? I think it's highly unlikely that he's got more than a
couple hundred bytes per row right now --- at least if the data is
what it sound
On 1-9-2005 19:42, Matthew Sackman wrote:
Obviously, to me, this is a problem, I need these queries to be under a
second to complete. Is this unreasonable? What can I do to make this "go
faster"? I've considered normalising the table but I can't work out
whether the slowness is in dereferencing t
On Thu, Sep 01, 2005 at 02:04:54PM -0400, Merlin Moncure wrote:
> > Table "public.address"
> > Column| Type | Modifiers
> > --++---
> > postcode_top | character varying(2) | not null
>
Matthew Sackman schrieb:
>Hi,
>
>I'm having performance issues with a table consisting of 2,043,133 rows. The
>schema is:
>
>\d address
> Table "public.address"
>Column| Type | Modifiers
>--++--
On Thu, Sep 01, 2005 at 03:51:35PM -0400, Merlin Moncure wrote:
> > Huh, hang on -- AFAIK there's no saving at all by doing that. Quite
> > the opposite really, because with char(x) you store the padding
> > blanks, which are omitted with varchar(x), so less I/O (not
> > necessarily a measurable
mance@postgresql.org
Subject: Re: [PERFORM] Massive performance issues
> I'm having performance issues with a table consisting of 2,043,133
rows.
> The
> schema is:
> locality_1 has 16650 distinct values and locality_2 has 1156 distinct
> values.
Just so you know I have a 2GHz p4
Matthew Sackman <[EMAIL PROTECTED]> writes:
> Obviously, to me, this is a problem, I need these queries to be under a
> second to complete. Is this unreasonable?
Yes. Pulling twenty thousand rows at random from a table isn't free.
You were pretty vague about your disk hardware, which makes me thi
On Thu, Sep 01, 2005 at 02:04:54PM -0400, Merlin Moncure wrote:
> > Any help most gratefully received (even if it's to say that I should
> be
> > posting to a different mailing list!).
>
> this is correct list. did you run vacuum/analyze, etc?
> Please post vacuum analyze times.
2005-09-01 19:47
On Thu, Sep 01, 2005 at 02:47:06PM -0400, Tom Lane wrote:
> Matthew Sackman <[EMAIL PROTECTED]> writes:
> > Obviously, to me, this is a problem, I need these queries to be under a
> > second to complete. Is this unreasonable?
>
> Yes. Pulling twenty thousand rows at random from a table isn't free
> Table "public.address"
> Column| Type | Modifiers
> --++---
> postcode_top | character varying(2) | not null
> postcode_middle | character varying(4) | not null
> postcode_b
> I'm having performance issues with a table consisting of 2,043,133
rows.
> The
> schema is:
> locality_1 has 16650 distinct values and locality_2 has 1156 distinct
> values.
Just so you know I have a 2GHz p4 workstation with similar size (2M
rows), several keys, and can find and fetch 2k rows b
Hi,
I'm having performance issues with a table consisting of 2,043,133 rows. The
schema is:
\d address
Table "public.address"
Column| Type | Modifiers
--++---
postcode_top | character
33 matches
Mail list logo
