D
= 123123;
181.944 ms
Query2:
select count(*) from Upload NATURAL JOIN UploadField Where
Upload.UploadID = 123123;
1136.024 ms
Greetings,
Jim J.
---
Details:
PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Li
I really have no idea what term I could use to force goggle to give
me solution ;)
Greetings,
Jim J.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
disk mirror.
Jim
-- Original Message ---
From: Greg Stark <[EMAIL PROTECTED]>
To: Alex Turner <[EMAIL PROTECTED]>
Cc: Greg Stark <[EMAIL PROTECTED]>, Arshavir Grigorian <[EMAIL PROTECTED]>,
linux-raid@vger.kernel.org,
pgsql-performance@postgresql.org
Sen
How about
SELECT p_id, f_id
FROM
person as p
LEFT JOIN
(SELECT f.p_id, max(f.id), f_item
FROM food) as f
ON p.p_id = f.p_id
Create an index on Food (p_id, seq #)
This may not gain any performance, but worth a try. I don't have any
data similar
create function abc() returns setof RECORD ...
then to call it you would do
select * from abc() as (a text,b int,...);
-- Original Message ---
From: Yves Vindevogel <[EMAIL PROTECTED]>
To: pgsql-performance@postgresql.org
Sent: Tue, 22 Nov 2005 19:29:37 +0100
Subject: [PERFORM]
ilar, means hitting the platter.
So I don't see how enabling the disk cache will help, unless of
course it's ignoring fsync.
Now, I have heard something about drives using their stored
rotational energy to flush out the cache... but I tend to suspect
urban legend t
l also tune the costs if reads
vs. writes are a concern.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 9: In versions belo
oat,avg_15 float);
The Sys::Statistics::Linux has all kind of info (from the /proc) file
system.
Jim
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dan Harris
Sent: Tuesday, March 20, 2007 8:48 PM
To: PostgreSQL Performance
Subject: [PERFORM] Determin
hich means the WAL files were 64MB instead of 16MB, but
even having 500 fragments for a 16MB WAL file seems like it would
definitely impact performance.
Can anyone else confirm this? I don't know if this is a windows-only
issue, but I don't know of a way to check fragmentation in
lena header. The only
reason I've ever used char in other databases is to save the overhead
of the variable-length information, so I recommend to people to just
steer clear of char in PostgreSQL.
--
Jim Nasby[EMAIL PROTECTED]
Enterp
fair comparison.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore
Adding -performance back in so others can learn.
On Apr 26, 2007, at 9:40 AM, Paweł Gruszczyński wrote:
Jim Nasby napisał(a):
On Apr 25, 2007, at 8:51 AM, Paweł Gruszczyński wrote:
where u6 stores Fedora Core 6 operating system, and u0 stores 3
partitions with ext2, ext3 and jfs filesystem
could stick a web page somewhere that would produce
a postgresql.conf based simply on how much available RAM you had,
since that's one of the biggest performance-hampering issues we run
into (ie: shared_buffers left at the default of 32MB).
--
Jim Nasby
configurations" could help.
Uh... what GUCs are that exacting on the amount of memory? For a
decent, base-line configuration, that is.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---
ct of vacuuming during the day (try
setting vacuum_cost_delay to 20 as a starting point).
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)--
use a small filesystem for pg_xlog and mount
that as ext2.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 6: explain analyze is
On May 7, 2007, at 11:10 PM, Yudhvir Singh Sidhu wrote:
Jim Nasby wrote:
On May 5, 2007, at 5:57 PM, Yudhvir Singh Sidhu wrote:
Problem: Vacuum times jump up from 45 minutes, or 1:30 minutes
to 6+ hours overnight, once every 1 to 3 months.
Solutions tried: db truncate - brings vacuum times
On May 27, 2007, at 12:34 PM, PFC wrote:
On Sun, 27 May 2007 17:53:38 +0200, Jim C. Nasby
<[EMAIL PROTECTED]> wrote:
On Tue, May 22, 2007 at 09:29:00AM +0200, PFC wrote:
This does not run a complete sort on the table. It would be
about as
fast as your seq scan disk throughput. Obv
this, but I
don't know if good controllers actually need to deal with things at a
stripe level, or if they can deal with smaller chunks of a stripe. In
either case, the issue is still the number of extra reads going on.
--
Jim Nasby[EMAIL PR
I really can't think of any reason you'd want to do that.
I do find vacuum_cost_delay to be an extremely useful tool, but
typically I'll set it to between 10 and 20 and leave the other
parameters alone.
--
Jim Nasby[EMAIL PRO
y and
set them to anything remotely close to 128GB.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 4: Have you searched our
is the ill-used -benchmarks list, but perhaps it would be
better if we setup a wiki for this...
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of
ing list cleanly
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
mber
of large tables, that could be a big problem, as autovac could get
tied up on a large table for a long enough period that the table
needing to be frozen doesn't get frozen in time.
I suspect 1B is a much better setting. I probably wouldn't g
ting in kernel cache, which would likely be miles
ahead of what's currently done.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ng at
OSDL; the only reason that was the case is because he had somewhere
around 70 data drives. I suppose an entirely in-memory database might
be able to swamp a 2 drive WAL as well.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprised
t I
want complete 8.2 -> 8.3 numbers).
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 5: don't forget to increase y
On Jul 20, 2007, at 1:03 PM, Josh Berkus wrote:
Jim,
Has anyone benchmarked HEAD against 8.2? I'd like some numbers to
use in my OSCon lightning talk. Numbers for both with and without
HOT would be even better (I know we've got HOT-specific
benchmarks, but I want complete
times. All other things remain the same. Which
means the only change I am performing is re-indexing.
Reindex will shrink index sizes, which will speed up vacuuming. But
that alone doesn't explain what you're seeing, which is rather odd.
--
od of overlap surrounding the time when you switch to a new
partition), you're looking at evaluating every input query twice.
In this case, the rules presumably are just simply re-directing DML,
so there'd only be one rule in play at a time. That means the only
real ov
Remove me from your email traffic.
> Date: Thu, 24 Jun 2010 23:05:06 -0400
> Subject: Re: [PERFORM] requested shared memory size overflows size_t
> From: robertmh...@gmail.com
> To: alvhe...@commandprompt.com
> CC: craig_ja...@emolecules.com; pgsql-performance@postgresql.org
>
> On Thu, Jun 24,
FROM bulk_performance.counts?
That will throw away the query results, which removes client-server
considerations.
Also, when you tested raw disk IO, did you do it with an 8k block size? That's
the default size of a Postgres block, so all of it's IO is done that way.
What does io
e the earlier email.
--
Jim C. Nasby, Database Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql
nd
> post, but the improvements via standard JDBC are such that we aren't really
> pressed at this point in time to get more throughput so it may not happen.
>
> Cheers,
>
> Nick
>> On 12/14/2010 9:41 AM, Jim Nasby wrote:
>>> On Dec 14, 2010, at 9:27 AM,
r data warehousing would be storing
the XIDs at the table level, because you'll typically have a very limited
number of transactions per table.
But as Robert mentioned, this is not easy to implement. The community would
probably need to see some pretty compelling performance numbers
into geometric shapes
and then index them accordingly. Prior to the work Jeff Davis has done on time
intervals it was common to treat time as points and ranges as lines or boxes.
While we no longer need to play those games for time, I don't think there's an
equivalent for non-time da
code, which is not an option for a production database.
Out of curiosity, have you tried using the information that Postgres exposes to
dtrace? I suspect it comes close to what you can get directly out of Oracle...
--
Jim C. Nasby, Database Architect j...@nasby.net
512.56
ce for larges tables.
Actually, this sounds more like having a foreign key pointed at a parent table
in an inheritance tree; which flat-out doesn't do what you'd want.
Can you tell us what the foreign key constraint actually is, and what the
inheritance setup for the tables in
ecutor that it can do 2 scans of
the index: one to get non-null data and a second to get null data. I don't know
if the use case is prevalent enough to warrant the extra code though.
--
Jim C. Nasby, Database Architect j...@nasby.net
512.569.9461 (cell)
with Postgresql 7.2.1 you will need to do BOTH vacuum and reindex and with a
table that gets many updates/deletes, you
should run vacuum more than daily.
Both issues have been solved in 8.1.
Jim
-- Original Message ---
From: Emmanuel Lacour <[EMAIL PROTECTED]>
To:
e of the slave machines. People who are doing
interactive work (updating data) will hit the master. Since most applications
do far more reading than they do writing, this is a pretty good way to
load-balance.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software
PostgreSQL tuned to the max and still too slow? Database too big to
fit into memory? Here's the solution! http://www.superssd.com/
products/tera-ramsan/
Anyone purchasing one will be expected to post benchmarks! :)
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Perv
go with design 1, update does = delete + insert.
-- Original Message ---
From: "Craig A. James" <[EMAIL PROTECTED]>
To: pgsql-performance@postgresql.org
Sent: Mon, 20 Mar 2006 14:49:43 -0800
Subject: [PERFORM] update == delete + insert?
> I've seen it said here several times that
as Josh said, as long as your somewhere in the ballpark it's probably
good enough.
--
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
-
It would absolutely help on the query in question. In my experience,
a correlation of 0.64 is too low to allow an index scan to be used
for anything but a tiny number of rows.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork
ce it, and otherwise does no harm with respect to
postgres' disk usage.
More importantly, it allows the system to come up and do fsck in the
background. If you've got a large database that's a pretty big benefit.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROT
Adding
-performance back in
-Original Message-From: Oscar Picasso
[mailto:[EMAIL PROTECTED]Sent: Wednesday, April 12, 2006 5:51
PMTo: Jim NasbySubject: Re: [PERFORM] Better index
stategy for many fields with few values
I would like to try it.However in an other post I
L PROTECTED]
> Sent: Wednesday, April 12, 2006 7:48 PM
> To: Jim Nasby
> Subject: RE: [PERFORM] multi column query
>
>
> I executed enable_seqscan=off and then ran an explain plan on
> the query
>
> UPDATE chkpfw_tr_dy_dimension
>
found; it appears that there's no information on how many heap
blocks were read in by an index scan. Is there any way to get that info?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.ne
. And yes, you'd have to ensure you didn't code yourself up a
trigger loop.
--
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
---
Adding -performance back in...
> From: Steve Poe [mailto:[EMAIL PROTECTED]
> Jim,
>
> I could be way off, but doesn't from pg_statio_user_tables
> contain this
> information?
http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS
st
memory. SCSI is out of our price
range, but if I had unlimited $ I would go
with SCSI /SCSI raid instead.
Jim
-- Original Message ---
From: "Simon Dale" <[EMAIL PROTECTED]>
To:
Sent: Thu, 20 Apr 2006 14:18:58 +0100
Subject: [PERFORM] Quick Performance Poll
>
and small tables
Jim
-- Original Message ---
From: "Luke Lonergan" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], "Simon Dale" <[EMAIL PROTECTED]>,
pgsql-performance@postgresql.org
Sent: Thu, 20 Apr 2006 07:31:33 -0700
Subject: Re: [PERFORM] Quick Pe
lt;[EMAIL PROTECTED]>,
pgsql-performance@postgresql.org
Sent: Thu, 20 Apr 2006 08:03:10 -0700
Subject: Re: [PERFORM] Quick Performance Poll
> Jim,
>
> On 4/20/06 7:40 AM, "Jim Buttafuoco" <[EMAIL PROTECTED]> wrote:
>
> > First of all this is NOT a single tab
Jan,
I write queries like this
CREATE VIEW parent_childs AS
SELECT
c.parent,
count(c.state) as childtotal,
sum(case when c.state = 1 then 1 else 0 end) as childstate1,
sum(case when c.state = 2 then 1 else 0 end) as childstate2,
sum(case when c.state = 3 t
I don't think an index will help you with this query.
-- Original Message ---
From: Jan Dittmer <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: pgsql-performance@postgresql.org
Sent: Fri, 21 Apr 2006 14:35:33 +0200
Subject: Re: [PERFORM] Better way to write aggrega
Please cc the list so others can help.
> From: Witold Strzelczyk [mailto:[EMAIL PROTECTED]
> On Friday 12 May 2006 00:04, you wrote:
>
> Yes, thanks but method is not a point.
Actually, it is a point. Databases don't like doing things procedurally. Using
a stored procedure to operate on a set
ds, so that startup cost
becomes
the name of the game. Instead, compare startup and total costs fuzzily
but independently. This changes the plan selected for two queries in
the
regression tests; adjust expected-output files for resulting changes in
row order. Per reports from Dawid Kuroczko
1024) in that table, then it's not surprising that an index
would help things.
--
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-5
Forwarding to -performance
From: Alan Hodgson [mailto:[EMAIL PROTECTED]
On Friday 09 June 2006 12:41, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> Has anyone actually done any testing on this? Specifically, I'm
> wondering if the benefit of adding 2 more drives to a
On Jun 13, 2006, at 8:50 PM, 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
current planner does fold "now() - interval_
n the code
would. Or you could just create 3 test functions and see what you end
up with, but I can't see it being any different from your guess.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http:
ught there were some technical
issues that had yet to be resolved?
BTW, I'll point out that DB2 and MSSQL didn't switch to MVCC until
their most recent versions.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.com
quality of the RAID controller also makes a huge difference.
--
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
--
Jim C. Nasby, Sr. Engineering Co
PostgreSQL @ Sun
San Francisco
---(end of
broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that
your
message can get through to the mailing list cleanly
--
Jim C. Nas
On Jun 16, 2006, at 12:01 PM, Josh Berkus wrote:
First thing as soon as I have a login, of course, is to set up a
Buildfarm
instance.
Keep in mind that buildfarm clients and benchmarking stuff don't
usually mix well.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROT
On Jun 21, 2006, at 5:53 PM, Ron St-Pierre wrote:
Jim C. Nasby wrote:
On Wed, Jun 21, 2006 at 02:27:41PM -0700, Ron St-Pierre wrote:
We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM,
raid 4, RHEL, postgres 8.1) and ported our old database over to
it (single cpu,
RAID *4
r two, I
started wondering how many sites could beat 200 databases in a single
cluster. I'm sure there's any number that can, though 200 databases in a
cluster certainly isn't mainstream.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software htt
possible. If not, the commit_delay settings might help you out.
There may be some further gains to be had by tweaking the background writer
settings; it might be too aggressive in your application.
That update statement could also be causing a lot of activity, depending on
what it's doing.
--
s, which sounds
way, way too slow. I suspect that index got bloated badly at some
point by not vacuuming frequently enough (autovacuum is your friend).
Try reindexing and see if that fixes the problem.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software
e server's log (> 500 ms), then
I know an analyze is needed, or statistics should be set higher.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)
scans after all?
Based on your initial post, it probably should know that it's only
getting 15 rows (since it did in your initial plan), so it's unclear
why it's not choosing the bitmap scan.
Can you post the results of EXPLAIN A
expended to optimize for that case (especially the resulting monster
UNION ALL), but you might get lucky.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)--
age/tuple ID, which would essentially do what
you're talking about. I don't know if it actually happened or not,
though.
If this is something that interests you, I recommend taking a look at
the code; it's generally not too hard to read through thanks to all
the comments.
you're not inadvertently disabling ACIDity in MySQL/
InnoDB; some options/performance tweaks will do that last I looked.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
--
sviews as-is, the code there should be very helpful
for doing that.
There is no ability to put triggers on DDL, so the best you could do
with your caching table is to just periodically update it.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http:/
tch that allows the planner to look into
what a subselect will return to us. ;)
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
--
Jim Nasby[EMAIL PRO
raps these emails, such as mailman.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner w
it about what it
actually does...
Better yet, have an unsubscribe page...
Personally, I'm tempted to get creative with procmail, and post a
recipe that others can use to help enlighten those that post
unsubscribe messages to the list... :>
--
Jim C. Nasby, Database Architect
You just lost that useful work.
(oh, btw, we didn't really beat up the programmers ... too big
geographical distances ;-)
This warrants a plane ticket. Seriously. If your app programmers
aren't versed in transaction management, you should probably be
complaints and made him shut it down.
--
Jim C. Nasby, Database Architect [EMAIL PROTECTED]
512.569.9461 (cell) http://jim.nasby.net
---(end of broadcast)---
TIP 1: if posting/reading through Usenet,
g that way regardless of where my paycheck comes
from) While it's important that we continue to improve the planner,
it's simply not possible to build one that's smart enough to handle
every single situation.
--
Jim C. Nasby, Database Architect [EMAIL PROT
queuing, which will
eventually do what you want. Take a look at the BizGres mailing list
archives for more info.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
the past) is to
define Start and End as a box, and then use the geometric functions
built into plain PostgreSQL (though perhaps that's what he meant by
"PostGIS stuff").
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterpris
On Nov 14, 2006, at 1:11 PM, Merlin Moncure wrote:
On 11/14/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
> On 11/14/06, Cosimo Streppone <[EMAIL PROTECTED]> wrote:
> >I must say I lowered "shared_buffers&
When I went to 7.4.3 (Slackware 9.1) w/ JDBC, the improvements are that it doesn't
initially take much memory (have 512M) and didn't swap. I ran a full vaccum and a
cluster before installation, however speed degaded to 1 *second* / update of one row
in 150 rows of data, within a day! pg_autovac
IL PROTECTED]
Cc: [EMAIL PROTECTED]
Date: Tue, 13 Jul 2004 16:26:09 -0400
Subject: Re: [PERFORM] Swapping in 7.4.3
Jim Ewert wrote:> When I went to 7.4.3 (Slackware 9.1) w/ JDBC, the improvements
are that it doesn't initially take much memory (have 512M) and didn't swap. I ran a
minor
increase, but not a lot. Incidentally, could anyone quantify that in
any fashion?
Thanks,
-Jim
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
original (x,x,x) (select temp.1, temp.2, etc from temp left
join original on temp.street=original.street where original.street is null)
Good Luck
Jim
Rudi Starcevic wrote:
Hi,
I have a question on bulk checking, inserting into a table and
how best to use an index for performance.
The data I have
Hugo,
I think your problem is with the MRS_TRANSACTION TRANS table. It is
not joining anything when declared, but later it is joining thru a LEFT
JOIN of the REPL_DATA_OWNER_RSDNC table. In fact I'm not sure that this
table is really needed. I would suggest rewriting your FROM clause. It
to evict a page from shared buffers that page gets
compressed and stuffed into a memcache cluster. When PG determines that a given
page isn't in shared buffers it will then check that memcache cluster before
reading the page from disk. This allows you to cache amounts of data that far
excee
are much higher). Unplanned
downtime on that database would cost us well over $100k/hour, and we're storing
financial information, so data quality issues are not an option (data quality
was one of the primary reasons we moved away from MySQL in 2006). So yes, you
can absolutely run very lar
lly
take fractions of a second, this kind of performance degradation can easily
bring a server to it's knees. Every time this has happened the solution has
been to re-analyze a fairly large table; even with default stats target of 1000
it's very easy for one bad analyze to ruin your day.
ing like correlation?
Hmm... it would be interesting if we had average relation access times for each
stats bucket on a per-column basis; that would give the planner a better idea
of how much IO overhead there would be for a given WHERE clause.
--
Jim C. Nasby, Database Architect j
your values are
decent-sized strings, the overhead is going to be many times larger than the
actual data!
--
Jim C. Nasby, Database Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-performance mailing list (pgsql-
On May 19, 2011, at 9:53 AM, Robert Haas wrote:
> On Wed, May 18, 2011 at 11:00 PM, Greg Smith wrote:
>> Jim Nasby wrote:
>>> I think the challenge there would be how to define the scope of the
>>> hot-spot. Is it the last X pages? Last X serial values? Som
x27; ... 'FROM ' ||
c_parent_oid::regclass
- you can also query directly with the OID: SELECT relkind = 't' AS is_table
FROM pg_class WHERE oid = c_parent_oid
--
Jim C. Nasby, Database Architect j...@nasby.net
512.569.9461 (cell) http
own version of it.
BTW, thanks to the compression feature of IC I've heard it can actually be
beneficial to run it on the same server.
--
Jim C. Nasby, Database Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent v
On Aug 17, 2011, at 1:49 AM, Eyal Wilde wrote:
> 1. is there any more elegant solution?
Very possibly, but I'm having a heck of a time trying to figure out what your
current code is actually doing.
What's the actual problem you're trying to solve here?
--
Jim C. Nasby,
ct the load time here, but could affect other queries.
>
> Actually on a heavily written database a large effective cache size
> makes things slower.
effective_cache_size or shared_buffers? I can see why a large shared_buffers
could cause problems, but what effect does effective_c
1 - 100 of 897 matches
Mail list logo
