On Thu, Jun 01, 2006 at 02:04:46PM -0400, Michael Artz wrote:
> On 5/30/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
>
> >Your best bet is to do this as a single, bulk operation if possible.
> >That way you can simply do an UPDATE ... WHERE EXISTS followed by an
> >I
The other thing you'll
want to do is cut all the vacuum threshold and scale settings in half
(the defaults are very conservative).
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net
o report how far along the query got, it seems that
could always be added to the explain output.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 5
ode that's using all the time up?
>
> That would be extremely useful.
Maybe, maybe not. It would be very easy for this to croak on the first
sort it hits. I suspect the original proposal of aborting once a
rowcount estimate proves to be way off is a better idea.
For the record, I also th
a long time though...
> Postgresql version we are using is -- PostgreSQL 7.2.4
You very badly need to upgrade. 7.2 is no longer supported, and there
have been over a half-dozen data loss bugs fixed since then.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software
> Cheers
> Antoine
>
>
> --
> This is where I should put some witty comment.
>
> -------(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive
On Thu, Jun 08, 2006 at 11:39:48AM +0530, soni de wrote:
> We have to take a backup of database and we know the pg_dump utility of
> postgresql.
>
> But may I know, is there any API for this pg_dump utility so that we can
> call it from the C program? Or only script support is po
n't fairly constant you can g
et some pretty bad plans. Unfortunately, no one's figured out a good way
to fix this yet.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.
nally with pipes attached
> to stdin/out/err, but a library implementation would solve some
> headaches (esp. concerning portability) managing background
> execution/GUI updates/process control. I'd like a libpgdumprestore
> library, with pg_dump/pg_restore being lean wrappe
do the sort.
> You'd need a two-column index on both of the ORDER BY columns to avoid
> sorting.
And even then you better have a pretty high correlation on the first
column, otherwise you'll still get a seqscan.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pe
became bigger and
> perfomance will decrease even more.
>
> How to increase perfomance ?
Do you have effective_cache_size set correctly? You might try dropping
random_page_cost down to 2 or so.
Of course you could just put more memory in the machine, too.
--
Jim C. Nasby, Sr. Engineering
ade to 8.1.4. There's a number of data loss bugs waiting
to bite you.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-94
ing pg_xlog on that RAID10 with all
the rest of the data.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
-
n other tables, etc,
> etc.
>
> Am I missing something here?
Read about function stability in the docs.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.
ory to look in for your
database.
> On 6/9/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> >On Fri, Jun 09, 2006 at 02:23:04PM +0200, Domenico - Sal. F.lli Riva
> >wrote:
> >> Hello,
> >>
> >> During insert or update, potgresql write
.log
> date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
Ugh. Is there some reason you're not using the built-in autovacuum? If
you enable it and cut the thresholds in half you'll most likely never
need to vacuum manually, let alone reindex.
--
Jim C. Nasby, Sr. Engineering Consultant
CUUM?
For the next vacuum, can you add the -v (verbose) switch and email the
last few lines of output?
INFO: free space map contains 39 pages in 56 relations
DETAIL: A total of 896 page slots are in use (including overhead).
896 page slots are required to track all free space.
Current limits are: 20
On Mon, Jun 12, 2006 at 05:22:05PM +0200, Ruben Rubio Rey wrote:
> Jim C. Nasby wrote:
>
> >On Mon, Jun 12, 2006 at 04:58:49PM +0200, Ruben Rubio Rey wrote:
> >
> >
> >>$DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN
> >>$DIRLOGS/%Y-%m-
mpany here in Austin I can put you in
touch with; if I'm not mistaken on the cost of pSeries hardware their
fee would be well worth it to make sure you don't end up with too much
(or worse, too little) hardware for your load.
> BTW, I promise, at the end of my tests, I'll publish my
n the CPU and the compiler. I can say
that in the 32 vs 64 bit benchmarking I've done using dbt2, I wasn't
able to find a difference at all on Sunfire Opteron machines.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512
k_mem settings. If there's additional changes to the shared
buffer code in 8.2 (I know Tom's been looking at doing multiple buffer
pools to reduce contention on the BufMgr lock), it'd be worth
re-benchmarking when it comes out.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAI
nd their approach to be pretty stupid.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)
On Tue, Jun 13, 2006 at 12:44:17PM -0700, Joshua D. Drake wrote:
> You can get extremely competitive quotes from IBM or HP as long as you
> say, "You are competing against Dell".
Possibly even more competitive from Sun...
--
Jim C. Nasby, Sr. Engineering Consultant
hours to fix/run a
> borken Dell box...
Would probably sound like a Mastercard commercial...
Not having to babysit your servers every day: Priceless
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comw
gt; Leapfrog Online
> 807 Greenwood Street
> Evanston, IL 60201
Heh, I grew up 3 miles from there. In fact, IIRC my old dentist is/was
at 807 Davis.
> Tel. 847-440-8253
> Fax. 847-570-5750
> www.leapfrogonline.com
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive S
On Tue, Jun 13, 2006 at 04:35:41PM -0500, Shaun Thomas wrote:
> >>> On 6/13/2006 at 4:13 PM, "Jim C. Nasby" <[EMAIL PROTECTED]>
> wrote:
>
>
> > Is there some compelling reason to stick with 7.4? In my experience
> > you'll see around d
ct it'd cost quite a bit less than a comperable
p570...
> We've been thrilled with the performance of our DB2 systems that run on
> AIX/Power 5 but since the DB2 instance memory is limited to 18GB, we've got
> two 86GB p570s sitting there being under utilized.
--
Jim C. Na
On Tue, Jun 13, 2006 at 06:04:42PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Also, I'm not sure that the behavior is entirely changed, either. On a
> > 8.1.4 database I'm still seeing a difference between now() - interval
>
On Tue, Jun 13, 2006 at 06:21:21PM -0400, John Vincent wrote:
> On 6/13/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> >On Tue, Jun 13, 2006 at 05:40:58PM -0400, John Vincent wrote:
> >> Maybe from a postgresql perspective the cpus may be useless but the
> >
On Tue, Jun 13, 2006 at 03:21:34PM -0700, Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> ...
> >Actually, in 8.1.x I've seen some big wins from greatly increasing the
> >amount of shared_buffers, even as high as 50% of memory, thanks to the
> >changes made
On Tue, Jun 13, 2006 at 05:41:06PM -0500, Shaun Thomas wrote:
> >>> On 6/13/2006 at 4:54 PM, "Jim C. Nasby" <[EMAIL PROTECTED]>
> wrote:
>
> > SELECT attname, attstattarget
> > FROM pg_attribute
> > WHERE attrelid='table_name':
On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> >On Tue, Jun 13, 2006 at 03:21:34PM -0700, Mischa Sandberg wrote:
> >>Raised shared_buffers to 16000 (128MB).
> >>DB server dropped to a CRAWL.
> >>
> >>
On Tue, Jun 13, 2006 at 05:01:34PM -0700, Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> >On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
> >>Jim C. Nasby wrote:
> >>>What's sort_mem set to? I suspect you simply ran the machine out of
> &g
On Tue, Jun 13, 2006 at 09:50:49PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > On Tue, Jun 13, 2006 at 06:04:42PM -0400, Tom Lane wrote:
> >> It'd depend on the context, possibly, but it's easy to show that the
> >>
xlog. Only
after you have those results would I consider trying to do things like
split indexes from tables, etc.
BTW, you should consider reserving some of the drives in the array as
hot spares.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://
On Wed, Jun 14, 2006 at 01:30:10PM +0200, B?sz?rm?nyi Zolt?n wrote:
> Replacing random() with a true constant gives me index scan
> even if it's hidden inside other function calls. E.g.:
The database has no choice but to compute random() for every row; it's
marked VOLATILE.
--
just compress after the
fact (though, that could just put you in a situation where pg_dump
becomes bandwidth constrained).
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf
On Wed, Jun 14, 2006 at 05:18:14PM -0400, John Vincent wrote:
> On 6/14/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> >On Wed, Jun 14, 2006 at 02:11:19PM -0400, John Vincent wrote:
> >> Out of curiosity, does anyone have any idea what the ratio of actual
> &
ifferent after all.
Yes... I was expecting that since we're looking at a date field that the
timestamp would get cast to a date. Sorry I wasn't clear on that...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-23
uff takes further steps
(such as flashing the cache contents to flash memory on a power
failure).
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell
On Thu, Jun 15, 2006 at 06:31:02AM +0200, Zoltan Boszormenyi wrote:
> Jim C. Nasby ?rta:
> >On Wed, Jun 14, 2006 at 01:30:10PM +0200, B?sz?rm?nyi Zolt?n wrote:
> >
> >>Replacing random() with a true constant gives me index scan
> >>even if it's h
t use aggregate function in index expression
decibel=#
BTW, there have been a number of proposals to negate the effect of not
having visibility info in indexes. Unfortunately, none of them have come
to fruition yet, mostly because it's a very difficult problem to solve.
But it is somethi
27;ve
generally seen it double the performance of 7.4.
Also, output from EXPLAIN ANALYZE would make it a lot easier to figure
out what the issue is, and it would be good to try this without
selecting any of the arrays.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Softw
ON t0(substr(code,1,2));
(yeah, I know t0 is an alias, but I already snipped the table name)
and
AND t1.datum >= '1/1/2005'
(might need to cast that to a date or whatever).
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.com
ggested would be a good idea.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)-
t; TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive
5.6M dead rows and 1.3M live rows.
I think you should forget about having autovacuum keep this table
in-check and add manual vacuum commands to your code. Autovac is
intended to deal with 99% of use cases; this is pretty clearly in the 1%
it can't handle.
--
Jim C. Nasby, Sr. Engineering Con
the machine with the problem?
The issue with autovac is that it will only vacuum one table at a time,
so if it's off vacuuming some other table for a long period of time it
won't be touching this table, which will be a problem. Now, if that's
actually what's happening...
--
ll be turned back on)
> checkpoint_segments = 64
> checkpoint_timeout = 1800
> effective_cache_size = 7
>
> [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax
> 66000
>
> We want to put this into production soon, but this is a showstopper. Can
> anyone help
ase, at least for this example.
BTW, you'll have a hard time convincing people to increase the cost
estimates of index scans, because experience has shown that they're
already too high (all the discussions about dropping random_page_cost,
for example).
--
Jim C. Nasby, Sr. Enginee
oading
the data? What hardware is this? What version of the database?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---
t; Regards,
> -Robert
>
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Jim C. Nasby, Sr. Engineering
er tweaking by both MySQL AB and Sun).
>
> I would love to get my hands on that postgresql version and see how much
> farther it could be optimized.
I'd love to get an english translation that we could use for PR.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL
a
> PostgreSQL environment. Metadata is enough, given that we log data on
> WAL anyway.
Actually, according to http://en.wikipedia.org/wiki/Ext3 the default
journalling option for ext3 isn't to journal the data (which is actually
data=journal), but to wait until the data is written before
Postgres 8.1.4
Slony 1.1.5
Linux manny 2.6.12-10-k7-smp #1 SMP Fri Apr 28 14:17:26 UTC 2006 i686
GNU/Linux
We're seeing an average of 30,000 context-switches a sec. This problem
was much worse w/8.0 and got bearable with 8.1 but slowly resurfaced.
Any ideas?
procs ---memory--
Tom Lane wrote:
We're seeing an average of 30,000 context-switches a sec. This problem
was much worse w/8.0 and got bearable with 8.1 but slowly resurfaced.
Is this from LWLock or spinlock contention? strace'ing a few backends
could tell the difference: look to see how many select(0,...) yo
Tom Lane wrote:
Sorry, I was unclear: it's the age of your oldest transaction that
counts (measured by how many xacts started since it), not how many
cycles it's consumed or not.
With the 8.1 code it's possible for performance to degrade pretty badly
once the age of your oldest transaction exc
re all
> >*much* higher than on the Sun box. The average I/O wait was 50% of one
> >CPU, which is huge. By comparison there was virtually no I/O wait on
> >the Sun machine.
> >
> >This is indicating that your HP machine is indeed I/O bound and
> >furthermore is t
g more than whatever the stripe size is).
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)
% and iowait 95%
>
> ps ax | grep post gives me
>
> postgres: postgres db [local] VACUUM
>
> Is there some solution to avoid such cases?
Have you turned on vacuum_cost_delay?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http
remely simplistic. IMO all of
that knowledge was made obsolete when 8.0 came out, and our handling of
shared_buffers has improved ever further since then. This is definately
an area that could use a lot more testing.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pe
y do better than 6+2.
Note that some controllers (such as 3ware) need to periodically test the
life of the BBU, and they disable write caching when they do so, which
would tank performance.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasiv
On Wed, Aug 09, 2006 at 04:50:30PM -0500, Scott Marlowe wrote:
> On Wed, 2006-08-09 at 16:35, Jim C. Nasby wrote:
> > On Wed, Aug 09, 2006 at 10:15:27AM -0500, Scott Marlowe wrote:
> > > Actually, the BIGGEST win comes when you've got battery backed cache on
> > >
s...
You haven't given us nearly enough information. What kind of hardware is
this? RAID? What changes have you made to postgresql.conf?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.ne
t it would make up for most filesystem
performance issues associated with pg_xlog as well.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
--
nteresting. IIRC, XFS is lower performing than ext3, so if your
previous tests were done with XFS, that might be part of it. But without
a doubt, if you don't have a good raid controller you don't want to try
combining pg_xlog with PGDATA.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL
entire DB in RAM. So, you'll want to increase
> effective_cache_size and reduce random_page_cost.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the post
u can make it happen, moving to 8.1.4 would almost certainly net a
noticable performance gain. I've seen 50-100% improvements, but how much
gain you'll actually see is highly workload dependent.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software
is deliberate, it's a
> >>cross-reference.
> >
> >The same column and the same table?
> >
> >Same column different table I could understand but not the same column
> >& table ;)
> >
> >I'm sure there's a reason for
On Mon, Aug 14, 2006 at 01:03:41PM -0400, Michael Stone wrote:
> On Mon, Aug 14, 2006 at 10:38:41AM -0500, Jim C. Nasby wrote:
> >Got any data to back that up?
>
> yes. that I'm willing to dig out? no. :)
Well, I'm not digging hard numbers out either, so that's
On Mon, Aug 14, 2006 at 01:09:04PM -0400, Michael Stone wrote:
> On Mon, Aug 14, 2006 at 12:05:46PM -0500, Jim C. Nasby wrote:
> >Wow, interesting. IIRC, XFS is lower performing than ext3,
>
> For xlog, maybe. For data, no. Both are definately slower than ext2 for
> xlog, which
reeBSD) it lasts 20minuts.
> > >
> > > Do you know if there is any way to tune the server or optimize this
> > > sentence?
> > >
> > > Thanks
> > > Sebasti?n Baioni
>
>
>
>
>
> __
On Tue, Aug 15, 2006 at 03:02:56PM -0400, Michael Stone wrote:
> On Tue, Aug 15, 2006 at 02:33:27PM -0400, [EMAIL PROTECTED] wrote:
> >On Tue, Aug 15, 2006 at 01:26:46PM -0400, Michael Stone wrote:
> >>On Tue, Aug 15, 2006 at 11:29:26AM -0500, Jim C. Nasby wrote:
> >>
cause you didn't set the kernel's shared memory settings
high enough.
To answer you other question:
http://archives.postgresql.org/pgsql-performance/2006-08/msg00095.php
> On 8/15/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >See the recent thread about how old rules of thumb fo
bunch of well known names). They all thought he was off his
rocker. Not too long after that, an unplanned power outage did occur,
and PostgreSQL was the only RDBMS that recovered every single database
without intervention.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive
htly lower than 2GB for postgresql to start successfully.
>
> Carl
>
> On 8/15/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >On Tue, Aug 15, 2006 at 12:47:54PM -0600, Carl Youngblood wrote:
> >> I tried setting it to 2GB and postgres wouldn't start. Didn'
your best bet for setting effective_cache_size is to use the
total memory in the machine, and substract some overhead for the OS and
other processes. I'll typically subtract 1G.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.com
On Tue, Sep 12, 2006 at 03:33:08PM -0400, [EMAIL PROTECTED] wrote:
> Hello All
>
> I am getting this message in my log files for my database.
>
> LOG: out of file descriptors: Too many open files; release and retry.
>
> At some point the memomy didn't get released and the postmaster reset itsel
On Tue, Sep 12, 2006 at 04:17:34PM -0600, Joshua Marsh wrote:
> data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
> v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
> FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s ON v.dsiacctno =
> s.dsiacctno;
>
On Thu, Sep 14, 2006 at 11:23:01AM -0400, Francisco Reyes wrote:
> My setup:
> Freebsd 6.1
> Postgresql 8.1.4
> Memory: 8GB
> SATA Disks
>
> Raid 1 10 spindles (2 as hot spares)
> 500GB disks (16MB buffer), 7200 rpm
> Raid 10
>
> Raid 2 4 spindles
> 150GB 10K rpm disks
> Raid 10
>
> shared_buff
On Wed, Sep 20, 2006 at 05:47:41PM +0200, Chris Mair wrote:
>
> > I am running bechmark test in a 50 GB postgresql database.
> > I have the postgresql.conf with all parameters by default.
> > In this configuration the database is very, very slow.
> >
> > Could you please tell which is the best co
On Thu, Sep 21, 2006 at 11:12:45AM -0400, Tom Lane wrote:
> yoav x <[EMAIL PROTECTED]> writes:
> > I've applied the following parameters to postgres.conf:
>
> > max_connections = 500
> > shared_buffers = 3000
> > work_mem = 10
> > effective_cache_size = 30
You just told the database
On Thu, Sep 21, 2006 at 08:46:41PM -0700, Luke Lonergan wrote:
> Mark,
>
> On 9/21/06 8:40 PM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
>
> > I'd advise against using this call unless it can be shown that the page
> > will not be used in the future, or at least, that the page is less useful
On Thu, Sep 21, 2006 at 11:05:39PM -0400, Bruce Momjian wrote:
> We tried posix_fadvise() during the 8.2 development cycle, but had
> problems as outlined in a comment in xlog.c:
>
> /*
> * posix_fadvise is problematic on many platforms: on older x86 Linux
> * it just dumps core, and
On Wed, Sep 27, 2006 at 10:51:26AM -0500, Dave Dutcher wrote:
> To make the planner prefer an index scan over a seq scan, I would first
> check the statistics again, and then you can try setting enable_seqscan to
> false (enable_seqscan is meant more for testing than production) or, you
> could try
Periodically taking longer is probably a case of some other process in
the database holding a lock you need, or otherwise bogging the system
down, especially if you're always running acctmessage from the same
connection (because the query plans shouldn't be changing then). I'd
suggest looking at wh
mething else to think about for both cases.
If you're certain that only one process will be performing DML on those
tables at any given time, then what you have is safe. But if that's the
case, I'm thinking you should be able to group things into chunks, which
should be more effic
On Wed, Sep 27, 2006 at 02:17:23PM -0800, Matthew Schumacher wrote:
> Jim C. Nasby wrote:
> >
> > It can cause a race if another process could be performing those same
> > inserts or updates at the same time.
>
> There are inserts and updates running all of the time
On Thu, Sep 28, 2006 at 11:28:43AM -0400, Bill Moran wrote:
> In response to Matthew Schumacher <[EMAIL PROTECTED]>:
> >
> > What I really need is a way to profile my proc when it runs slow so that
> > I can resolve which of the queries is really slow. Anyone with an idea
> > on how to do this?
>
On Thu, Sep 28, 2006 at 10:11:31AM -0700, Joshua D. Drake wrote:
> > 4) Can anyone recommend any commercial PostgreSQL service providers that
> > may
> > be able to swiftly come in and assist us with our performance issues?
>
> http://www.commandprompt.com/ (disclaimer, I am an employee)
You f
On Thu, Sep 28, 2006 at 01:47:44PM -0400, Carlo Stonebanks wrote:
> > How are you loading the tables? Copy? Insert?
>
> Once the data is transformed, it is inserted. I don't have stats, but the
> programs visual feedback does not spend a lot of time on the "inserting
> data" message. Then again,
On Thu, Sep 28, 2006 at 01:53:22PM -0400, Carlo Stonebanks wrote:
> > are you using the 'copy' interface?
>
> Straightforward inserts - the import data has to transformed, normalised and
> de-duped by the import program. I imagine the copy interface is for more
> straightforward data importing.
pgsql-jdbc@postgresql.org
Bcc: pgsql-performance@postgresql.org
Subject: Re: RES: [PERFORM] Performace Optimization for Dummies
Reply-To:
In-Reply-To: <[EMAIL PROTECTED]>
X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64
X-Distributed: Join the Effort! http://www.distributed.net
Please start a ne
On Thu, Sep 28, 2006 at 02:04:21PM -0700, Steve Atkins wrote:
> I think you're confusing "explain" and "analyze". "Explain" gives you
> human readable output as to what the planner decided to do with the
> query you give it.
Don't forget about EXPLAIN ANALYZE, which is related to EXPLAIN but has
Instead of the IN, see if this is better:
AND (SELECT count(*) FROM albumjoin aj WHERE aj.album = album.id)
BETWEEN 15 AND 25.
>From a design standpoint, it probably makes sense to have a track_count
field in the album table that is kept up-to-date by triggers on
albumjoin.
And some nits. :)
I
There's no join criteria for umdb.node... is that really what you want?
On Thu, Sep 28, 2006 at 03:18:56PM -0700, Ben wrote:
> Hey guys, I've got a query that is inherently expensive, because it has to
> do some joins against some large tables. But it's currently *very*
> expensive (at least for
On Sat, Sep 30, 2006 at 02:55:54PM +0530, Nimesh Satam wrote:
> I am trying to vaccum one of the table using the following command:
>
> VACUUM FULL ANALYZE VERBOSE table_name;
Are you sure you want to do a vacuum full? Normally, that shouldn't be
required.
--
Jim Nasby
On Sun, Oct 01, 2006 at 11:01:19PM -0400, Tom Lane wrote:
> Steve Martin <[EMAIL PROTECTED]> writes:
> > I am trying to determine if there is a way to improve the performance
> > when selecting data from the information_schema.columns view.
>
> In my experience, there isn't any single one of the
Adding -performance back in.
On Tue, Oct 03, 2006 at 05:10:04PM -0700, Ron Mayer wrote:
> Jim C. Nasby wrote:
> >
> > Index scans are also pretty picky about correlation. If you have really
> > low correlation you don't want to index scan,
>
> I'm still d
On Thu, Oct 05, 2006 at 09:30:45AM -0400, Merlin Moncure wrote:
> I personally only use explicit joins when doing outer joins and even
> them push them out as far as possible.
I used to be like that too, until I actually started using join syntax.
I now find it's *way* easier to identify what the
601 - 700 of 819 matches
Mail list logo
