Thread: SLOOOOOOOW
Hi all, I working now for a wile with postgres (7.4), and I have the impression that is one of the slowest dbms with which I've aver worked. Can please somebody explain to me, why this is the case? I mean, the flexibility postgres offers is great, and I really appreciate it. But if you take advantage of this it is just nearly unusable because it is so slow. For instance I have some views, which gather some data from a few tables, and in several occasions there are other views operation on these views. It is f.... slow. And I have already created indices and the query are using these indices. It can't be tha case, that a few queries, already optimzed, have such a lousy performance. And I'm only talking about select statements. For insert statements the performance is even worse. OK, I'm using rules on a view to update some tables, but hey, it cant be that updating 1 (one!!!) record takes around 3 seconds, thats unusable! With best regards Juergen Rose
Am Montag, 5. September 2005 09:39 schrieb Rose, Juergen: > I working now for a wile with postgres (7.4), and I have the impression > that is one of the slowest dbms with which I've aver worked. Can please > somebody explain to me, why this is the case? I suggest you read the performance tips chapter of the documentation. For slow-running specific queries, post the output of EXPLAIN ANALYZE. Also, typical mistakes for a disproportionally slow database would be never having run VACUUM or ANALYZE. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Sep 5, 2005, at 4:39 PM, Rose, Juergen wrote: > I working now for a wile with postgres (7.4), and I have the > impression > that is one of the slowest dbms with which I've aver worked. Can > please > somebody explain to me, why this is the case? You've given very few details as to your database schema, size, use- case (e.g, heavy updates, mostly reads), load, hardware, etc. Are you vacuuming regularly? Have you tuned your database? Have you set your stats appropriately? With more detail, such as queries (with EXPLAIN ANALYZE output), you may be able to get some help on the pgsql- performance list. Without any real information, people aren't going to be able to help you. Here are some references that may help. There are more out there as well. I'm nowhere near an expert on tuning and performance, but these can get you started. http://www.powerpostgresql.com/PerfList/ http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Good luck! Michael Glaesemann grzm myrealbox com
# Juergen.Rose@sag-el.com / 2005-09-05 09:39:47 +0200:
> I working now for a wile with postgres (7.4), and I have the impression
> that is one of the slowest dbms with which I've aver worked. Can please
> somebody explain to me, why this is the case?
Because the default configuration (is | seems to be) aimed at PDAs.
You'll need to configure PostgreSQL for real world use.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
> Have you set your stats appropriately? I've been wondering about how high to set these - is there any performance downside to pumping up the statistics count on a table? I presume ANALYZE will take longer, and the planner might take a tiny bit longer, but otherwise, it can't hurt, correct? Thanks. - John D. Burger MITRE
john@mitre.org ("John D. Burger") writes:
>> Have you set your stats appropriately?
>
> I've been wondering about how high to set these - is there any
> performance downside to pumping up the statistics count on a table?
> I presume ANALYZE will take longer, and the planner might take a
> tiny bit longer, but otherwise, it can't hurt, correct?
Ten times bigger means collecting ~10x the stats, so yeah, that'll
take longer. The planner shouldn't take materially longer; it's a
"bump up" in the number of "bins."
The *real* issue is whether you can afford to *not* increase the
stats...
The usual default is 10 bins in the histogram.
It seems rare for increasing the number of bins to help change query
plans unless the number of bins is increased to 100 or more.
I'd be inclined to jump straight to 100 for cases where the query
planner seems to be getting things wrong with stats set to 10...
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/wp.html
"Of course, to join the NSA, all you really have to do is pick up any
phone, anywhere, make a long-distance call, and say `National Security
Agency, I'd like a job.' They'll check you out and either give you a
job, or do a job on you." -- David Palmer