Re: [BUGS] Index speeds up one row table (why)?

2003-05-31 Thread Bruno Wolff III
On Sat, May 31, 2003 at 00:14:18 -0600, Dave E Martin XXIII <[EMAIL PROTECTED]> wrote: > version: 7.3.2 > > Currently this table has one row in it, where name is 15 unicode > characters long. It would seem that there would be no need for an index > on name. However, doing: It probably has one

[BUGS] Index speeds up one row table (why)?

2003-05-31 Thread Dave E Martin XXIII
version: 7.3.2 Ok, not really sure if this a bug per se, but its non-intuitive, and it goes against the advice stated in the user guide (page 150, "...there is no plan that can beat sequentially fetching 1 page...") I have an application that performs many inserts in a second (its doing real-t

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Robert Creager
On Sat, 31 May 2003 00:11:26 -0400 Tom Lane <[EMAIL PROTECTED]> said something like: > > Cool ... but it's not immediately obvious which of these changes did the > trick for you. What settings were you at before? And what's the > details of the problem query? > > The first three settings you me

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Tom Lane
Robert Creager <[EMAIL PROTECTED]> writes: > Thanks for the hint. I just upped my shared_buffers to 8192, fsm_relations to > 1, fsm_pages to 10, sort_mem to 64000, and an UPDATE which was taking over 2 > hours dropped down to 1 to 2 minutes! Cool ... but it's not immediately obvious wh

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Robert Creager
On Fri, 30 May 2003 09:11:39 -0400 Tom Lane <[EMAIL PROTECTED]> said something like: > > Also note that no amount of vacuuming will save you if the FSM is not > large enough to keep track of all the free space. The default FSM > settings, like all the other default settings in Postgres, are set

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Tomas Szepe
> [EMAIL PROTECTED] > > Additionally, you may want to take a look at your query performance. Are > most of your queries doing sequential scans? In my system, the crucial > columns of the primary tables are int8 and float8 fields. I have those > indexed, and I get a serious performance boost by

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Tomas Szepe
> As Tom said, you probably need higher FSM settings, but also, do you have > any long lived transactions (say from some kind of persistent connection > system) that might be preventing vacuum from removing rows? No, not at all. -- Tomas Szepe <[EMAIL PROTECTED]> ---(end

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Tomas Szepe
> [EMAIL PROTECTED] > > Peter Childs <[EMAIL PROTECTED]> writes: > > On Fri, 30 May 2003, Tomas Szepe wrote: > >> Trouble is, as the rows in the tables get deleted/inserted/updated > >> (the frequency being a couple thousand rows per minute), the database > >> is growing out of proportion in size.

[BUGS] "PostgreSQL BugTool page": MIA?

2003-05-31 Thread Ian Barwick
The FAQ lists a "PostgreSQL BugTool page" at: http://www.PostgreSQL.org/bugs/bugs.php which I dimly recall seeing at one point, but it seems to have dropped off the server. I would guess this has something to do with the recent changes; the last posts to pgsql-bugs which carry a number, which I

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Stephan Szabo
On Fri, 30 May 2003, Tomas Szepe wrote: > > [EMAIL PROTECTED] > > > > > Trouble is, as the rows in the tables get deleted/inserted/updated > > > (the frequency being a couple thousand rows per minute), the database > > > is growing out of proportion in size. After about a week, I have > > > to r

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Todd Nemanich
I have a database with similar performance constraints. Our best estimates put the turnover on our most active table at 350k tuples/day. The hardware is a 4x1.4GHz Xeon w/ a RAID 1 disk setup, and the DB floats around 500MB of disk space taken. Here is what we do to maintain operations: 1) Cro