I've posted several emails, and have yet to see one
show up (this one might not either).
Is there a size limit to an email (it had a big
analyze, and schema information)??
David
We're having a problem with a query during our
investigation into Postgres (as an Oracle replacement). This query Postgres
takes 20-40 seconds (multiple runs). Tom Lan recommended I post it here, with an
explain-analyze.
Here's the query:
EXPLAIN ANALYZE SELECT company_name, address_1,
Here's the schema:
Table "public.address_list"
Column
|
Type |
Modifiers--++--- address_list_id
| numeric(10,0) | not
null address_1
| character varying(100)
|
And finally,
Here's the contents of the postgresql.conf file
(I've been playing with these setting the last couple of days, and using the
guide @ http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html to
make sure I didn't have it mis-tuned):
tcpip_socket = truemax_conn
> The most efficient way to handle this query would probably be to join
> the three tables with restrictions first, and then join the other tables
> to those. You could force this with not too much rewriting using
> something like (untested, but I think it's right)
>
> ... FROM commercial_entity C
This is a timely thread for myself, as I'm in the
middle of testing both databases as an Oracle replacement.
As of this moment, I know more about MySQL (tuning,
setup, features) than I do about Postgres. Not because I like MySQL more, but
because
1) the MySQL docs are better (sorry - I fo
> Thanks for being considerate, thourough, and honest about your opinions.
> Particulary that you didn't simple depart in a huff.
Why would I depart in a huff? I was just trying to make a few objective
observations.
I really have no biases; I like what I've seen in MySQL, and I like alot of
the m
I am running an update-query to benchmark various databases; the
postgres version is,
UPDATE user_account SET last_name = 'abc'WHERE user_account_id IN
(SELECT user_account_id FROM commercial_entity, commercial_service WHERE
yw_account_id IS NULLAND commercial_entity.commercial_entity_id =
1));
...
}
Unfort, that will be alot of data moved from
Postgres->middle-tier (Weblogic/Resin), which is inefficient.
Anyone see another solution?
David.
----- Original Message -
From:
David
Griffiths
To: [EMAIL PROTECTED]
Sent: Saturday, October 11, 2003 12:44
Thanks - that worked.
David
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "David Griffiths" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, October 11, 2003 3:34 PM
Subject: Re: [PERFORM] Another weird one with an
[snip]
> I think you want something like:
> UPDATE user_account SET last_name = 'abc'
> WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
> WHERE user_account.user_account_id = ce.user_account_id AND
> ce.commercial_entity_id = cs.commercial_entity_id);
Unfort, this is st
ON ON DELETE NO ACTION,
$3 FOREIGN KEY (user_role_id) REFERENCES
user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION
David
- Original Message -
From: "Joe Conway" <[EMAIL PROTECTED]>
To: "David Griffiths" <[EMAIL PROTEC
..2220.86
rows=72483 loops=1)
-> Index Scan using comm_ent_usr_acc_id_i on
commercial_entity ce (cost=0.00..4787.69 rows=78834 width=24) (actual
time=0.02..55.64 rows=7991 loops=1)
Total runtime: 226239.77 msec
(10 rows)
David
- Original Message -
From: "Stephan Sz
> And what about commercial_entity.user_account_id. Is it indexed and what
> is its data type (i.e. does it match numeric(10,0))?
Yup - all columns in the statement are indexed, and they are all
numeric(10,0).
> Also, have you run VACUUM ANALYZE lately?
Yup - just before the last run.
Will get
I've been having performance issues with Postgres
(sequential scans vs index scans in an update statement). I've read that
optimizer will change it's plan based on the resources it thinks are available.
In addition, I've read alot of conflicting info on various parameters, so I'd
like to sor
15 matches
Mail list logo
