Hi-
I'm trying to optimize a query that I *think* should run very fast.
Essentially, I'm joining two tables that have very selective indexes and
constraining the query on an indexed field. (There's a third small lookup
table in the mix, but it doesn't really affect the bottom line.)
actor is a
I am working on migrating to postgres and had some questions regarding
optimization that I could not find references in the documentation:
1. Is there any performance difference for declaring a primary or
foreign key a column or table contraint? From the documentation, which
way is faster
I'm running PG 7.2.2 on RH Linux 8.0.
I'd like to know why VACUUM ANALYZE table is extemely slow (hours) for
certain tables. Here's what the log file shows when I run this command on
my employees table, which has just 5 columns and 55 records:
VACUUM ANALYZE employees
DEBUG: --Relation
I've tried to measure the duration of sql with printing out
localtimestamp but for some reason during the same pg/plsql call
it returns the same value:
Aram,
From http://www.postgresql.org/docs/current/static/functions-datetime.html:
There is also the function timeofday(), which for
Chadwick, Russell kirjutas L, 13.12.2003 kell 00:40:
Hello everyone.
Can anyone explain why this table which has never had more than a
couple rows in it shows 500k in the query planner even after running
vacuum full. Its terribly slow to return 2 rows of data. The 2 rows
in it are being
Sean P. Thomas [EMAIL PROTECTED] writes:
1. Is there any performance difference for declaring a primary or
foreign key a column or table contraint? From the documentation,
which way is faster and/or scales better:
CREATE TABLE distributors (
did integer,
name
David Shadovitz [EMAIL PROTECTED] writes:
I'm running PG 7.2.2 on RH Linux 8.0.
Note that this version of PostgreSQL is quite old.
I'd like to know why VACUUM ANALYZE table is extemely slow (hours) for
certain tables.
Is there another concurrent transaction that has modified the table
but
I have stored procedure written in pl/pgsql which takes about 13 seconds
to finish. I was able to identify that the slowness is caused by one
update SQL:
UPDATE shopping_cart SET sc_sub_total=sc_subtotal, sc_date=now()
WHERE sc_id=sc_id;
If I comment this sql out, the stored procedure
On Tue, 16 Dec 2003, Jenny Zhang wrote:
I have stored procedure written in pl/pgsql which takes about 13 seconds
to finish. I was able to identify that the slowness is caused by one
update SQL:
UPDATE shopping_cart SET sc_sub_total=sc_subtotal, sc_date=now()
WHERE sc_id=sc_id;
Umm, is
Oops, I named the var name the same as the column name. Changing it to
something else solved the problem.
Thanks,
Jenny
On Tue, 2003-12-16 at 15:54, Stephan Szabo wrote:
On Tue, 16 Dec 2003, Jenny Zhang wrote:
I have stored procedure written in pl/pgsql which takes about 13 seconds
to
1. Is there any performance difference for declaring a primary or
foreign key a column or table contraint? From the documentation, which
way is faster and/or scales better:
CREATE TABLE distributors (
did integer,
namevarchar(40),
PRIMARY KEY(did)
);
CREATE TABLE
On Tue, 16 Dec 2003, Nick Fankhauser wrote:
Is there a more efficient means than a nested loop to handle such a join?
Would a different method be chosen if there was exactly one row in
actor_summary for every row in actor?
As a question, what does explain analyze give you if you
set
Bruce Momjian wrote:
write 0.000360
write fsync 0.001391
write, close fsync 0.001308
open o_fsync, write0.000924
That's 1 milliseconds vs. 1.3 milliseconds. Neither value is realistic -
I guess the hw cache on and the os doesn't issue cache flush
Running the attached test program shows on BSD/OS 4.3:
write 0.000360
write fsync 0.001391
I think the write fsync pays for the previous write test (same filename).
write, close fsync 0.001308
open o_fsync, write0.000924
I have
Neil,
Thanks for the good advice. I noticed that I had some sessions for which I
could not account, and I think even a 2nd postmaster running. It looks like
I've cleaned everything up, and now I can VACUUM and I can DROP an index which
wouldn't drop.
And I'm looking into upgrading
I backed up my database using pg_dump, and then restored it onto a different
server using psql. I see that the query SELECT COUNT(*) FROM myTable
executes immediately on the new server but takes several seconds on the old
one. (The servers are identical.)
What could account for this
Neil Conway wrote:
How can I get the original server to perform as well as the new one?
Well, you have the answer. Dump the database, stop postmaster and restore it.
That should be faster than original one.
(BTW, SELECT count(*) FROM table isn't a particularly good DBMS
performance
On Tue, 16 Dec 2003, David Shadovitz wrote:
I backed up my database using pg_dump, and then restored it onto a different
server using psql. I see that the query SELECT COUNT(*) FROM myTable
executes immediately on the new server but takes several seconds on the old
one. (The servers are
18 matches
Mail list logo
