From: | "Webb Sprague" <webb(dot)sprague(at)gmail(dot)com> |
---|---|
To: | "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Database slowness -- my design, hardware, or both? |
Date: | 2007-03-06 17:45:33 |
Message-ID: | b11ea23c0703060945x4b5c7796qff07addeca6ae80a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am not able to look as closely as it deserves ...
... but I see two seq scans in your explain in a loop -- this is
probably not good. If you can find a way to rewrite the IN clause
(either de-normalizing through triggers to save whatever you need on
an insert and not have to deal with a set, or by using except in the
query, or someing else more creative)...
Also -- there is a good book on temporal databases by Snodgrass that
might give some interesting ideas; maybe you have already seen it, but
still. I am thinking you could increment a sequence variable to give
you a "tick" integer with each action, rather than trying to use
timestamps with all their overhead and inaccuracy (1 second is a long
time, really). Lamport also did work on clocks that might apply.
Also have you tried dropping all your fk and checks just to see if you
get a difference in speed on an update? It would be interesting,
perhaps.
If you could get rid of the sorted limit 1 clause in your function,
there would be less variablity and make it easier to understand; you
probably need to denormalize somehow, perhaps using ticks; I am not
sure....
Could a trigger set your previous_value on insert or update, rather
than querying for it later?
> I'm now thinking of separating each activity into its own database, in
> the hopes that this will improve the system speed enough to do what I
> need. But I'm far from convinced that this will really help.
Better to figure out the real problem -- more interesting, more scalable.
Hope my incoherent brain dump lights a spark.
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2007-03-06 17:54:08 | Re: Importing *huge* mysql database into pgsql |
Previous Message | Jeff Ross | 2007-03-06 17:37:22 | Re: Postgres Mailing List management solution |