Title: Postgres respond after toomany times to a query view
Hi, I am developing a program using postgres and linux like operating system. My problem is this:
I have a quite complicated view with roughly 1 record. When I execute a simple query like this
"select * from myview"
postg
On Sat, 13 Dec 2003, Kari Lavikka wrote:
>
> Hi!
>
> We have been running a rather busy website using pg 7.3 as the database.
> Peak hitrate is something like 120 request / second without images and
> other static stuff. The site is a sort of image gallery for IRC users.
>
> I evaluated pg 7.4
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 ta
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 and/
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 updated a lot but I couldn't find any
explanation for
I'm running PG 7.2.2 on RH Linux 8.0.
I'd like to know why "VACUUM ANALYZE " 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 employe
> 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 h
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 ta
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
"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,
> namev
"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 " is extemely slow (hours) for
> certain tables.
Is there another concurrent transaction that has modified the table
bu
On Tue, Dec 16, 2003 at 12:11:59PM -0500, Nick Fankhauser wrote:
>
> 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
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 return
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, i
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
> > t
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 distribu
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 enable
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 command
> 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.000
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 PostgreSQL
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 differe
David Shadovitz <[EMAIL PROTECTED]> writes:
> What could account for this difference?
Lots of things -- disk fragmentation, expired tuples that aren't being
cleaned up by VACUUM due to a long-lived transaction, the state of the
kernel buffer cache, the configuration of the kernel, etc.
> How can
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 indication.
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
24 matches
Mail list logo
