Re: [PERFORM] Suspending SELECTs

Tom Lane wrote: Mark Kirkwood <[EMAIL PROTECTED]> writes: SELECT ... FROM table WHERE ... ORDER BY id LIMIT 20; Suppose this displays records for id 1 -> 10020. When the user hits next, and page saves id=10020 in the session state and executes: SELECT ... FROM table WHERE ... AND i

Re: [PERFORM] Multiple Order By Criteria

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Rabu, 18 Januari 2006 07:23 To: Stephan Szabo Cc: Josh Berkus; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Multiple Order By Criteria I've read all of this info, closely.

Re: [PERFORM] Getting pg to use index on an inherited table (8.1.1)

Hari Warrier <[EMAIL PROTECTED]> writes: > A query on foobar2 (child table) uses the index, whereas the same query > via foobar (parent) doesn't. A query just on foobar should be able to use the index AFAIR. The problem here is that you have a join, and we are not very good about situations invo

Re: [PERFORM] Suspending SELECTs

Mark Kirkwood <[EMAIL PROTECTED]> writes: > SELECT ... FROM table WHERE ... ORDER BY id LIMIT 20; > Suppose this displays records for id 1 -> 10020. > When the user hits next, and page saves id=10020 in the session state > and executes: > SELECT ... FROM table WHERE ... AND id > 10020 ORDER

Re: [PERFORM] Suspending SELECTs

Alessandro Baretta wrote: I think you're trying to do something at the wrong layer of your architecture. This task normally goes in your middleware layer, not your database layer. I am developing my applications in Objective Caml, and I have written the middleware layer myself. I could easil

[PERFORM] Getting pg to use index on an inherited table (8.1.1)

Hi, I have two tables foobar and foobar2 (which inherits from foobar, no extra columns). foobar2 has all the data (574,576 rows), foobar is empty. Both foobar and foobar2 have an index on the only column 'id'. Now I have a list of ids in a tmp_ids tables. A query on foobar2 (child table) uses

Re: [PERFORM] Suspending SELECTs

[EMAIL PROTECTED] wrote: On Tue, Jan 17, 2006 at 08:56:00PM +0100, Alessandro Baretta wrote: What is wrong with LIMIT and OFFSET? I assume your results are ordered in some manner. Especially with web users, who become bored if the page doesn't flicker in a way that appeals to them, how could o

Re: [PERFORM] Multiple Order By Criteria

I've read all of this info, closely. I wish when I was searching for an answer for my problem these pages came up. Oh well. I am getting an idea of what I need to do to make this work well. I was wondering if there is more information to read on how to implement this solution in a more simple way

Re: [PERFORM] Ensuring data integrity with fsync=off

On Sat, Jan 14, 2006 at 01:41:43PM -0500, Tom Lane wrote: > "Benjamin Arai" <[EMAIL PROTECTED]> writes: > > Right now I run "sync" afte the updates have finished to ensure that the > > data is synced to disk but I am concerned about the segment data and > > anything else I am missing that PostgreSQ

Re: [PERFORM] Multiple Order By Criteria

On Tue, 17 Jan 2006 [EMAIL PROTECTED] wrote: > I created the index like this: > > CREATE INDEX rcbee_idx > ON detail_summary > USING btree > (receipt, carrier_id, batchnum, encounternum, encounter_id); > > Is this correct ? That would work if you were asking for all the columns ascending o

Re: [PERFORM] Suspending SELECTs

People: To follow up further, what Alessandro is talking about is known as a "keyset cursor". Sybase and SQL Server used to support them; I beleive that they were strictly read-only and had weird issues with record visibility. -- --Josh Josh Berkus Aglio Database Solutions San Francisco -

Re: [PERFORM] Multiple Order By Criteria

I created the index like this: CREATE INDEX rcbee_idx ON detail_summary USING btree (receipt, carrier_id, batchnum, encounternum, encounter_id); Is this correct ? How do I make a reverse opclass ? - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Josh Berkus" Cc

Re: [PERFORM] Multiple Order By Criteria

try adding the keyword 'date' before the date in your query. I ran into this quite a while back, but I'm not sure I remember the solution. > In Reply to: Tuesday January 17 2006 04:29 pm, [EMAIL PROTECTED] [EMAIL > PROTECTED] wrote: > I created the index, in order. Did a vacuum analyze on th

Re: [PERFORM] Suspending SELECTs

Alessandro, > I understand most of these issues, and expected this kind of reply. > Please, allow me to insist that we reason on this problem and try to > find a solution. My reason for doing so is that the future software > industry is likely to see more and more web applications retrieving data

Re: [PERFORM] Suspending SELECTs

On Tue, 17 Jan 2006 16:12:59 -0500 [EMAIL PROTECTED] wrote: > In the mean time, I successfully use LIMIT and OFFSET without such an > optimization, and things have been fine for me. Same here. - Frank Wiles <[EMAIL PROTECTED]> http://www.wiles.org -

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote: > Well, > > I think that the documentation is not exactly easy to understand. I > always wondered why there are no examples for common postgresql > configurations. All I know is that the default configuration seems to be > too low f

Re: [PERFORM] Multiple Order By Criteria

On Tue, 17 Jan 2006, Josh Berkus wrote: > J, > > > I have an index built for each of these columns in my order by clause. > > This query takes an unacceptable amount of time to execute. Here are the > > results of the explain: > > You need a single index which has all five columns, in order. I t

Re: [PERFORM] Multiple Order By Criteria

I created the index, in order. Did a vacuum analyze on the table and my explain still says: Limit (cost=229610.78..229611.03 rows=100 width=717) -> Sort (cost=229610.78..230132.37 rows=208636 width=717) Sort Key: receipt, carrier_id, batchnum, encounternum, encounter_id -> Seq

Re: [PERFORM] Multiple Order By Criteria

J, > I have an index built for each of these columns in my order by clause. > This query takes an unacceptable amount of time to execute. Here are the > results of the explain: You need a single index which has all five columns, in order. -- --Josh Josh Berkus Aglio Database Solutions San Fran

[PERFORM] Multiple Order By Criteria

I'm trying to query a table with 250,000+ rows. My query requires I provide 5 colums in my "order by" clause:   select   column from table where  column >= '2004-3-22 0:0:0'order by     ds.receipt desc,     ds.carrier_id asc,     ds.batchnum asc,     encounternum asc,     ds.encounter_id AS

Re: [PERFORM] Suspending SELECTs

> I am developing my applications in Objective Caml, and I have written the > middleware layer myself. I could easily implement a cursor-pooling strategy, > but > there is no perfect solution to the problem of guaranteeing that cursors be > closed. Remember that web applications require the use

Re: [PERFORM] Suspending SELECTs

On Tue, Jan 17, 2006 at 08:56:00PM +0100, Alessandro Baretta wrote: > I understand most of these issues, and expected this kind of reply. Please, > allow me to insist that we reason on this problem and try to find a > solution. My reason for doing so is that the future software industry is > lik

Re: [PERFORM] sum of left join greater than its parts

Hmmm, this looks like a planner bug to me: > Hash > Join (cost=870.00..992.56 rows=1 width=96) (actual time=90.566..125.782 > rows=472 loops=1) Hash Cond: (("outer".host_id = "inner".host_id) AND > ("outer"."?column2?" = "inner".mtime)) -> HashAggregate > (cost=475.88..495.32 rows=1555 width=16

Re: [PERFORM] wildcard search performance with "like"

Yantao Shi <[EMAIL PROTECTED]> writes: > testdbspc=# explain select file_name from catalog where file_name like > 'MOD04_L2.A2005311.%.004.2005312013%.hdf'; > QUERY PLAN > Seq Scan on catalog (cost=0.00..429.00 rows=1 width=404) > Filter: (file_name ~~ 'MOD04_L2.A2005311.%.004.2005312013%.hdf':

Re: [PERFORM] Suspending SELECTs

On Tue, Jan 17, 2006 at 09:06:53PM +0100, Alessandro Baretta wrote: > Craig A. James wrote: > > > >Alessandro Baretta <[EMAIL PROTECTED]> writes: > > > >I think you're trying to do something at the wrong layer of your > >architecture. This task normally goes in your middleware layer, not > >your

Re: [PERFORM] big databases & hospitals

On Sat, Jan 14, 2006 at 01:13:02PM +0100, Tomka Gergely wrote: > Hi! > > I need to write a few pages about Postgresql, to convince some suits. They > have a few millions of records, on a few site, but they want to know the > practical limits of Postgresql. So i need some information about the > bi

Re: [PERFORM] Suspending SELECTs

Alessandro Baretta <[EMAIL PROTECTED]> writes: > * When the cursor state is pushed back to the backend, no new > transaction is instantiated, but the XID of the original transaction > is reused. In the MVCC system, this allows us to achieve a perfectly > consistent view of the database at the insta

[PERFORM] wildcard search performance with "like"

Hi, I have a postges 8.1.1 table with over 29 million rows in it. The colunm (file_name) that I need to search on has entries like the following: MOD04_L2.A2005311.1400.004.2005312013848.hdf MYD04_L2.A2005311.0700.004.2005312013437.hdf

Re: [PERFORM] Suspending SELECTs

Craig A. James wrote: Alessandro Baretta <[EMAIL PROTECTED]> writes: I think you're trying to do something at the wrong layer of your architecture. This task normally goes in your middleware layer, not your database layer. I am developing my applications in Objective Caml, and I have writt

Re: [PERFORM] Suspending SELECTs

On Tue, Jan 17, 2006 at 08:56:00PM +0100, Alessandro Baretta wrote: I understand most of these issues, and expected this kind of reply. Please, allow me to insist that we reason on this problem and try to find a solution. My reason for doing so is that the future software industry is likely to

Re: [PERFORM] Suspending SELECTs

Tom Lane wrote: Alessandro Baretta <[EMAIL PROTECTED]> writes: I am aware that what I am dreaming of is already available through cursors, but in a web application, cursors are bad boys, and should be avoided. What I would like to be able to do is to plan a query and run the plan to retreive a

Re: [PERFORM] Autovacuum / full vacuum

Chris Browne <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Alvaro Herrera) writes: >> Even a database-wide vacuum does not take locks on more than one table. >> The table locks are acquired and released one by one, as the operation >> proceeds. > Has that changed recently? I have always seen "

Re: [PERFORM] Autovacuum / full vacuum

[EMAIL PROTECTED] (Alvaro Herrera) writes: > Chris Browne wrote: >> [EMAIL PROTECTED] (Andrew Sullivan) writes: >> > On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote: >> >> hi, >> >> >> >> I'm curious as to why autovacuum is not designed to do full vacuum. I >> > >> > Because nothing

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 11:43:14AM -0500, Chris Browne wrote: > [EMAIL PROTECTED] (Andrew Sullivan) writes: > > Because nothing that runs automatically should ever take an exclusive > > lock on the entire database, > That's a bit more than what autovacuum would probably do... Or even VACUUM FULL

Re: [PERFORM] Autovacuum / full vacuum

On Tue, 2006-01-17 at 11:16, Andrew Sullivan wrote: > On Tue, Jan 17, 2006 at 09:59:25AM -0600, Scott Marlowe wrote: > > I have to admit, looking at the documentation, that we really don't > > explain this all that well in the administration section, and I can see > > how easily led astray beginner

Re: [PERFORM] Autovacuum / full vacuum

Chris Browne wrote: > [EMAIL PROTECTED] (Andrew Sullivan) writes: > > On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote: > >> hi, > >> > >> I'm curious as to why autovacuum is not designed to do full vacuum. I > > > > Because nothing that runs automatically should ever take an exclusi

Re: [PERFORM] Autovacuum / full vacuum

Hi, Michael, Michael Riess wrote: > But actually I never understood why the database system slows down at > all when there is much unused space in the files. Are the unused pages > cached by the system, or is there another reason for the impact on the > performance? No, they are not cached as su

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 09:59:25AM -0600, Scott Marlowe wrote: > I have to admit, looking at the documentation, that we really don't > explain this all that well in the administration section, and I can see > how easily led astray beginners are. I understand what you mean, but I suppose my reactio

[PERFORM] sum of left join greater than its parts

8.1.1, everything vacuumed/analyzed. basically i have two queries that when executed individually run quite quickly, but if I try to left join the second query onto the first, everything gets quite a bit slower. rms=# explain analyze rms-# SELECT rms-# software_download.* rms-# FROM rms-#

Re: [PERFORM] Autovacuum / full vacuum

[EMAIL PROTECTED] (Andrew Sullivan) writes: > On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote: >> hi, >> >> I'm curious as to why autovacuum is not designed to do full vacuum. I > > Because nothing that runs automatically should ever take an exclusive > lock on the entire database,

Re: [PERFORM] Autovacuum / full vacuum

On Tue, 2006-01-17 at 09:08, Andrew Sullivan wrote: > On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote: > > always wondered why there are no examples for common postgresql > > configurations. > > You mean like this one? (for 8.0): > >

Re: [PERFORM] Autovacuum / full vacuum

Michael Riess <[EMAIL PROTECTED]> writes: > But actually I never understood why the database system slows down at > all when there is much unused space in the files. Perhaps some of your common queries are doing sequential scans? Those would visit the empty pages as well as the full ones.

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 04:04:41PM +0100, Michael Riess wrote: > > I thought that vacuum full only locks the table which it currently > operates on? I'm pretty sure that once a table has been vacuumed, it can > be accessed without any restrictions while the vacuum process works on > the next ta

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 03:05:29PM +0100, Michael Riess wrote: > There must be a way to implement a daemon which frees up space of a > relation without blocking it too long. Define "too long". If I have a table that needs to respond to a SELECT in 50ms, I don't have time for you to lock my tabl

Re: [PERFORM] Autovacuum / full vacuum

Michael Riess <[EMAIL PROTECTED]> writes: > I'm curious as to why autovacuum is not designed to do full vacuum. Locking considerations. VACUUM FULL takes an exclusive lock, which blocks any foreground transactions that want to touch the table --- so it's really not the sort of thing you want bein

Re: [PERFORM] Autovacuum / full vacuum

On 1/17/06, Michael Riess <[EMAIL PROTECTED]> wrote: about the FSM: You say that increasing the FSM is fairly cheap - howshould I know that?comment from original postgresql.conf file seems pretty obvious:#max_fsm_pages = 2  # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 09:09:02AM -0500, Matthew T. O'Connor wrote: > vacuum. As long as that percentage is small enough, the effect on > performance is negligible. Have you measured to see if things are truly Actually, as long as the percentage is small enough and the pages are really empty,

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote: > always wondered why there are no examples for common postgresql > configurations. You mean like this one? (for 8.0): > All I know is that the default configurati

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote: about the FSM: You say that increasing the FSM is fairly cheap - how should I know that? Why would you assume otherwise, to the point of not considering changing the setting? The documentation explains how much memory is used for

Re: [PERFORM] Autovacuum / full vacuum

Hi, hi, I'm curious as to why autovacuum is not designed to do full vacuum. I Because nothing that runs automatically should ever take an exclusive lock on the entire database, which is what VACUUM FULL does. I thought that vacuum full only locks the table which it currently operates on?

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote: > hi, > > I'm curious as to why autovacuum is not designed to do full vacuum. I Because nothing that runs automatically should ever take an exclusive lock on the entire database, which is what VACUUM FULL does. > activity. Increasin

Re: [PERFORM] Autovacuum / full vacuum

Well, I think that the documentation is not exactly easy to understand. I always wondered why there are no examples for common postgresql configurations. All I know is that the default configuration seems to be too low for production use. And while running postgres I get no hints as to which

Re: [PERFORM] Autovacuum / full vacuum

> did you read my post? In the first part I explained why I don't want > to increase the FSM that much. No, you didn't. You explained *that* you thought you didn't want to increase the FSM. You didn't explain why. FSM expansion comes fairly cheap, and tends to be an effective way of eliminating

Re: [PERFORM] Autovacuum / full vacuum

Michael Riess wrote: > Hi, > > yes, some heavily used tables contain approx. 90% free space after a > week. I'll try to increase FSM even more, but I think that I will > still have to run a full vacuum every week. Prior to 8.1 I was using > 7.4 and ran a full vacuum every day, so the autovacuum ha

Re: [PERFORM] Autovacuum / full vacuum

Hi, yes, some heavily used tables contain approx. 90% free space after a week. I'll try to increase FSM even more, but I think that I will still have to run a full vacuum every week. Prior to 8.1 I was using 7.4 and ran a full vacuum every day, so the autovacuum has helped a lot. But actuall

Re: [PERFORM] Autovacuum / full vacuum

Hi, Matthew, Matthew T. O'Connor wrote: > I'm sure he did, but just because you don't have enough FSM space to > capture all everything from your "burst", that doesn't mean that space > can't be reclaimed. The next time a regular vacuum is run, it will once > again try to fill the FSM with any r

Re: [PERFORM] Autovacuum / full vacuum

Michael Riess wrote: did you read my post? In the first part I explained why I don't want to increase the FSM that much. I'm sure he did, but just because you don't have enough FSM space to capture all everything from your "burst", that doesn't mean that space can't be reclaimed. The next ti

Re: [PERFORM] Autovacuum / full vacuum

VACUUM FULL blocks the application. That is NOT something that anyone wants to throw into the "activity mix" randomly. There must be a way to implement a daemon which frees up space of a relation without blocking it too long. It could abort after a certain number of blocks have been freed a

Re: [PERFORM] Autovacuum / full vacuum

> I'm curious as to why autovacuum is not designed to do full vacuum. Because that's terribly invasive due to the locks it takes out. Lazy vacuum may chew some I/O, but it does *not* block your application for the duration. VACUUM FULL blocks the application. That is NOT something that anyone

Re: [PERFORM] new to postgres (and db management) and performance

Hi, Thomas, [EMAIL PROTECTED] wrote: >> Try a), b), and c) in order on the "offending" tables as they address >> the problem at increasing cost... > > thanks alot for the detailed information! the entire concept of vacuum > isn't yet that clear to me, so your explanations and hints are very much

Re: [PERFORM] new to postgres (and db management) and performance

Hi, Tom, Tom Lane wrote: >>in our db system (for a website), i notice performance boosts after a vacuum >>full. but then, a VACUUM FULL takes 50min+ during which the db is not really >>accessible to web-users. is there another way to perform maintenance tasks >>AND leaving the db fully operabl

Re: [PERFORM] new to postgres (and db management) and performance already a problem :-(

On Tue, Jan 17, 2006 at 09:14:27AM +0100, Antoine wrote: > think about it - we do very little removing, pretty much only inserts and > selects. I will give it a vacuum full and see what happens. UPDATES? Remember that, in Postgres, UPDATE is effectively DELETE + INSERT (from the point of view of

Re: [PERFORM] Autovacuum / full vacuum

Michael Riess wrote: > hi, > > I'm curious as to why autovacuum is not designed to do full vacuum. Because a VACUUM FULL is too invasive. Lazy vacuum is so light on the system w.r.t. locks that it's generally not a problem to start one at any time. On the contrary, vacuum full could be a disast

Re: [PERFORM] Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 11:33:02AM +0100, Michael Riess wrote: did you read my post? In the first part I explained why I don't want to increase the FSM that much. Since you didn't quantify it, that wasn't much of a data point. (IOW, you'd generally have to be seriously resource constrained befo

Re: [PERFORM] Autovacuum / full vacuum

>> You should never have to do full vacuums... I would rather say, You should never have to do full vacuums by any periodic means. It may be done on a adhoc basis, when you have figured out that your table is never going to grow that big again. On 1/17/06, Christopher Kings-Lynne <[EMAIL PROTECTE

[PERFORM] Use of Stored Procedures and

Hi, I already read the documentation for to use the SPI_PREPARE and SPI_EXEC... but sincerely I don't understand how I will use this resource in my statements. I looked for examples, but I din't good examples :(.. Somebody can help me? Thanks. Marcos. ---(end of bro

Re: [PERFORM] Autovacuum / full vacuum

Hi, did you read my post? In the first part I explained why I don't want to increase the FSM that much. Mike So my question is: What's the use of an autovacuum daemon if I still have to use a cron job to do full vacuums? wouldn't it just be a minor job to enhance autovacuum to be able to pe

Re: [PERFORM] Autovacuum / full vacuum

So my question is: What's the use of an autovacuum daemon if I still have to use a cron job to do full vacuums? wouldn't it just be a minor job to enhance autovacuum to be able to perform full vacuums, if one really wants it to do that - even if some developers think that it's the wrong approac

[PERFORM] Autovacuum / full vacuum

hi, I'm curious as to why autovacuum is not designed to do full vacuum. I know that the necessity of doing full vacuums can be reduced by increasing the FSM, but in my opinion that is the wrong decision for many applications. My application does not continuously insert/update/delete tuples at

Re: [PERFORM] new to postgres (and db management) and performance already a problem :-(

On 17/01/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Try a), b), and c) in order on the "offending" tables as they address> the problem at increasing cost...thanks alot for the detailed information! the entire concept of vacuum isn'tyet that clear to me, so your explanations and hints are ve