Re: [PERFORM] Index Scan Costs versus Sort

Charlie Savage <[EMAIL PROTECTED]> writes: > Out of curiosity, how much longer would an index_scan expected to be > versus a seq scan? I was under the impression it would be about a facto > of 4, or is that not usually the case? No, it can easily be dozens or even hundreds of times worse, in th

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> writes: > ... A graph showing the performance > characteristics is here: > I hadn't looked at this chart till just now, but it sure seems to put a crimp in my theory that you are running out of room to hold the indexes in RAM. That

Re: [PERFORM] Index Scan Costs versus Sort

Following up with some additional information. The machine has 1Gb physical RAM. When I run the query (with sort and seqscan enabled), top reports (numbers are fairly consistent): Mem: 1,032,972k total, 1,019,516k used, 13,412k free, 17,132k buffers Swap: 2,032,140k total, 17,592k used, 2

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> writes: > On Thu, 2005-11-10 at 17:18 -0500, Tom Lane wrote: >> Could you send me (off list) the complete gprof output files? > Sure, Thanks. Right offhand I can see no smoking gun here. The pg_detoast_datum entry I was worried about seems to be just measureme

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> writes: > I've finally gotten around to profiling the back end. Thanks for following up. The sudden appearance of pg_detoast_datum() in the top ten in the third profile is suspicious. I wouldn't expect that to get called at all, really, during a normal COPY IN

Re: [PERFORM] 8.x index insert performance

Second try... no attachment this time. I've finally gotten around to profiling the back end. Here is a more precise description of what I'm doing: I am copying data into two tables, order_main and order_transition (table defs at the end of this post). The order_transition table has roughly doub

Re: [PERFORM] 8.x index insert performance

On Tue, 2005-11-01 at 10:37 -0500, Ron Peacetree wrote: > I'm surprised that no one seems to have yet suggested the following > simple experiment: > > Increase the RAM 4GB -> 8GB, tune for best performance, and > repeat your 100M row insert experiment. > > Does overall insert performance change?

Re: [PERFORM] Index Scan Costs versus Sort

Hi Tom, From pg_stats: schema = "tiger"; tablename = "completechain"; attname = "tlid"; null_frac = 0; avg_width = 4; n_distinct = -1; most_common_vals = ; most_common_freqs = ; correlation = 0.155914; Note that I have default_statistics_target set to 100. Here is the first few values from hi

Re: [PERFORM] Outer Join performance in PostgreSQL

Hello Michael, Here is the information : I had executed explain analyze with modified FROM clause. Oops forgot to mention the version earlier. Using postgres 8.0.0 on Solaris 9. Rows Count : cic=# select count(*) from taxpack_user; count 172645 (1 row) cic=# select count(*) from ec

Re: [PERFORM] Index Scan Costs versus Sort

Charlie Savage <[EMAIL PROTECTED]> writes: > 1. Postgresql estimates the index scan will be 50 times more costly > than the seq scan (112870376 vs 2229858) yet in fact it only takes 3 > times longer to execute (2312426 s vs. 768403 s). My understanding is > that postgresql assumes, via the ran

Re: [PERFORM] WAL sync behaviour

On Thu, Nov 10, 2005 at 12:44:15PM -0500, Tom Lane wrote: > Don't think so ... want to write something up? Hard part is to > figure out where to put it ... To be honest, I think we could use a "newbie's guide to PostgreSQL performance tuning". I've seen rather good guides for query tuning, and gu

Re: [PERFORM] WAL sync behaviour

Scott Marlowe <[EMAIL PROTECTED]> writes: > Thanks for the clarification! Nice to know I can setup an ext2 > partition for my WAL files then. Is this in the docs anywhere? Don't think so ... want to write something up? Hard part is to figure out where to put it ... rega

Re: [PERFORM] same plan, add 1 condition, 1900x slower

Mitch Skinner <[EMAIL PROTECTED]> writes: > This is with Postgres 8.0.3. Any advice is appreciated. These are exactly the same plan, except for the addition of the extra filter condition ... >-> Index Scan using external_id_map_primary_key on external_id_map > eim (cost=0.00..2345747.01 ro

Re: [PERFORM] WAL sync behaviour

On Thu, Nov 10, 2005 at 11:39:34AM -0500, Tom Lane wrote: > No, Mike is right: for WAL you shouldn't need any journaling. This is > because we zero out *and fsync* an entire WAL file before we ever > consider putting live WAL data in it. During live use of a WAL file, > its metadata is not changi

[PERFORM] Index Scan Costs versus Sort

This is related to my post the other day about sort performance. Part of my problem seems to be that postgresql is greatly overestimating the cost of index scans. As a result, it prefers query plans that involve seq scans and sorts versus query plans that use index scans. Here is an example q

Re: [PERFORM] WAL sync behaviour

On Thu, 2005-11-10 at 10:39, Tom Lane wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > On Thu, 2005-11-10 at 08:43, Michael Stone wrote: > >> There's no reason to use a journaled filesystem for the wal. Use ext2 in > >> preference to ext3. > > > Not from what I understood. Ext2 can't guaran

[PERFORM] same plan, add 1 condition, 1900x slower

This is with Postgres 8.0.3. Any advice is appreciated. I'm not sure exactly what I expect, but I was hoping that if it used the external_id_map_source_target_id index it would be faster. Mainly I was surprised that the same plan could perform so much differently with just an extra condition. I

Re: [PERFORM] WAL sync behaviour

Scott Marlowe <[EMAIL PROTECTED]> writes: > On Thu, 2005-11-10 at 08:43, Michael Stone wrote: >> There's no reason to use a journaled filesystem for the wal. Use ext2 in >> preference to ext3. > Not from what I understood. Ext2 can't guarantee that your data will > even be there in any form after

Re: [PERFORM] Sort performance on large tables

We use this memory in all our servers (well - the 512 sticks). 0 problems to date: http://www.newegg.com/Product/Product.asp?Item=N82E16820145513 $163 for 1GB. This stuff is probably better than the Samsung RAM dell is selling you for 3 times the price. Alex On 11/10/05, Ron Peacetree <[EMAIL

Re: [PERFORM] Sort performance on large tables

My original post did not take into account VAT, I apologize for that oversight. However, unless you are naive, or made of gold, or have some sort of "special" relationship that requires you to, _NE VER_ buy RAM from your computer HW OEM. For at least two decades it's been a provable fact that O

Re: [PERFORM] WAL sync behaviour

On Thu, Nov 10, 2005 at 09:52:38AM -0600, Scott Marlowe wrote: Not from what I understood. Ext2 can't guarantee that your data will even be there in any form after a crash. It can if you sync the data. (Which is the whole point of the WAL.) I believe only metadata journaling is needed thou

Re: [PERFORM] WAL sync behaviour

On Thu, 2005-11-10 at 08:43, Michael Stone wrote: > On Thu, Nov 10, 2005 at 03:25:35PM +0100, Steinar H. Gunderson wrote: > >Ah, yes, I forgot -- it's ext3. We're considering simply moving the WAL onto > >a separate partition (with data=writeback and noatime) if that can help us > >any. > > There'

Re: [PERFORM] Some help on buffers and other performance tricks

On Thu, 2005-11-10 at 09:25, Frank Wiles wrote: > On Thu, 10 Nov 2005 09:16:10 -0600 > Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > Very valid point. It's the reason, in my last job, we had a mainline > > server with dual 2800MHz CPUs and a big RAID array. > > > > And our development, build an

Re: [PERFORM] WAL sync behaviour

On Thu, Nov 10, 2005 at 03:25:35PM +0100, Steinar H. Gunderson wrote: Ah, yes, I forgot -- it's ext3. We're considering simply moving the WAL onto a separate partition (with data=writeback and noatime) if that can help us any. There's no reason to use a journaled filesystem for the wal. Use ext

Re: [PERFORM] Some help on buffers and other performance tricks

> The point Gentlemen, was that Good Architecture is King. That's what I > was trying to emphasize by calling proper DB architecture step 0. All > other things being equal (and they usually aren't, this sort of stuff is > _very_ context dependent), the more of your critical schema that you can >

Re: [PERFORM] Some help on buffers and other performance tricks

On Thu, 10 Nov 2005 09:16:10 -0600 Scott Marlowe <[EMAIL PROTECTED]> wrote: > Very valid point. It's the reason, in my last job, we had a mainline > server with dual 2800MHz CPUs and a big RAID array. > > And our development, build and test system was a Dual Pentium Pro 200 > with 256 Meg of ram

Re: [PERFORM] Some help on buffers and other performance tricks

On Wed, 2005-11-09 at 22:20, Ron Peacetree wrote: > The point Gentlemen, was that Good Architecture is King. That's what I was > trying to emphasize by calling proper DB architecture step 0. All other > things being equal (and they usually aren't, this sort of stuff is _very_ > context depende

Re: [PERFORM] WAL sync behaviour

On Thu, Nov 10, 2005 at 02:14:30PM +, Richard Huxton wrote: > You're beyond my area of expertise, but I do know that someone's going > to ask what filesystem this is (ext2/xfs/etc). Ah, yes, I forgot -- it's ext3. We're considering simply moving the WAL onto a separate partition (with data=wr

Re: [PERFORM] WAL sync behaviour

Steinar H. Gunderson wrote: Hi, We're having problems with our PostgreSQL server using forever for simple queries, even when there's little load -- or rather, the transactions seem to take forever to commit. We're using 8.1 (yay!) on a single Opteron, with WAL on the system two-disk (software) R

[PERFORM] WAL sync behaviour

Hi, We're having problems with our PostgreSQL server using forever for simple queries, even when there's little load -- or rather, the transactions seem to take forever to commit. We're using 8.1 (yay!) on a single Opteron, with WAL on the system two-disk (software) RAID-1, separate from the datab

Re: [PERFORM] (View and SQL) VS plpgsql

FOR r_record IN SELECT count(cid) AS hits,src, bid, tid,NULL::int8 as min_time,NULL::int8 as max_time FROM archive_event WHERE inst=\'3\' AND (utctime BETWEEN \'111492\' AND \'1131512399\') GROUP BY src, bid, tid LOOP SELECT INTO one_record MIN(utctime) as timestart,MAX(u