Hello,
This is my first question on this list.
How does max_parallel_workers_per_gather change Linux server load averages?
I have 2 cores and my max_parallel_workers_per_gather = 2 and
max_worker_processes = 8, but my load averages are between 8 and 5 with
scheduled at 1/189 to 5/195. Are thes
On Aug 31, 2016, at 3:01 PM, Bobby Mozumder wrote:
>
> Is it possible to find the number of disk IOs performed for a query? EXPLAIN
> ANALYZE looks like it shows number of sequential rows scanned, but not number
> of IOs.
Postgres knows the number of rows it will need to pull to do your que
On Sep 9, 2015, at 3:43 AM, anil7385 wrote:
>
> Hi,
> We have a table which consists of 3 millions of records and when we try to
> delete them and run VACUUM VERBOSE ANALYZE on it in production environment ,
> it takes 6/7 hours to process.
You make it sound like you are deleting all records. I
definitely helps and the query performance goes back to normal,
thanks. It makes the code a bit more complicated, so not ideal, but
definitely works!
Thanks for the help. I don't how much you know about PostgreSQL internals
(I don't!), but what optimization would need to be in place for PostgreSQL
to be smarter about this query?
-Ben
oramas_property_id_fkey ON content.panoramas
(property_id);
CREATE INDEX panoramas_hotel_id_idx ON content.panoramas (hotel_id);
CREATE INDEX panoramas_import_id_idx ON content.panoramas (import_id);
--
Thanks in advance,
Ben
On Nov 26, 2013, at 9:24 AM, Craig James wrote:
> So far I'm impressed by what I've read about Amazon's Postgres instances.
> Maybe the reality will be disappointing, but (for example) the idea of
> setting up streaming replication with one click is pretty appealing.
Where did you hear this was
Hmm, weird -- now the RI_Initial_Check() query is much quicker (20s). We do
ANALYZE the data every few nights, so maybe that's what changed it. I'll
keep that in mind. -Ben
On Fri, Nov 1, 2013 at 3:19 AM, Tom Lane wrote:
> Ben Hoyt writes:
> >> It appears the possible
ng only one schema, and dropping/re-adding
constraints on deployment because of this. Is this a really strange
thing to do -- deploying only one schema (the "static" data) and
dropping/re-adding constraints -- or are there better practices here?
Relatedly, what about best practices regarding
ed recently (from 3-4 minutes to 7 minutes).
*
http://www.postgresql.org/message-id/20030323112241.w14634-100...@megazone23.bigpanda.com
-- indicates that ADD CONSTRAINT isn't optimized as well as it could
be
* http://www.postgresql.org/message-id/51a11c97.90...@iol.ie --
indicates that
On Jun 26, 2013, at 5:22 PM, Marcin Mańk wrote:
> On Wed, Jun 26, 2013 at 2:29 AM, Ben wrote:
>
>> shouldn't an index scan definitely be fastest here? you don't need to touch
>> the whole table or index. maybe there something i have misconfigured here?
>>
7566336 width=28)
Sort Key: (ROW(id1, id2, id3, id4, id5))
-> Seq Scan on bigtable (cost=0.00..76085300.36 rows=3167566336
width=28)
(4 rows)
(apologies bigtable has grown since i've first started this thread.) shouldn't
an index scan definitely be fastest here
ht now i'm interested in why the row estimates are off.
moving on to your remarks :
On Jun 25, 2013, at 6:20 AM, Kevin Grittner wrote:
> Ben wrote:
>
>> PostgreSQL 9.1.1 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
>> 4.6.2, 64-bit
>
> Consider applying the
variable
wal_buffers | 8MB| configuration file
wal_keep_segments| 128| configuration file
wal_level| hot_standby| configuration file
work_mem | 48MB | configuration file
(26 ro
amiss? unfortunately any synthetic
examples i was able to make (up to 10 million rows) did not exhibit this
behavior, which makes it hard to test.
best regards, ben
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Apr 12, 2013, at 9:45 AM, Rodrigo Barboza wrote:
> Hi guys.
> I compiled my postrges server (9.1.4) with default segment size (16MB).
> Should it be enough? Should I increase this size in compilation?
Unlike some default values in the configuration file, the compiled-in defaults
work well for
On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
> random_page_cost=1 might be not what you really want.
> it would mean that random reads are as fast as as sequential reads, which
> probably is true only for SSD
> What randon_page_cost would be more appropriate for EC2 EBS Provisioned
> volume
On Nov 30, 2012, at 8:06 AM, Shaun Thomas wrote:
> I say that because you mentioned you're using Ubuntu 12.04, and we were
> having some problems with PG on that platform. With shared_buffers over
> 4GB, it starts doing really weird things to the memory subsystem.
> Whatever it does causes the ker
On Nov 17, 2011, at 5:20 PM, Steve Atkins wrote:
>
> I don't think you want the group by in that first query.
Heh, I tried to simply the example, but in reality that = becomes an in clause
of multiple values. So the group by is needed.
>>
>>
>> postgres=# explain analyze select min(id) from
I have two queries in PG 9.1. One uses an index like I would like, the other
does not. Is this expected behavior? If so, is there any way around it?
postgres=# explain analyze select min(id) from delayed_jobs where
strand='sis_batch:account:15' group by strand;
On Nov 14, 2011, at 4:42 PM, Cody Caughlan wrote:
> We have anywhere from 60-80 background worker processes connecting to
> Postgres, performing a short task and then disconnecting. The lifetime
> of these tasks averages 1-3 seconds.
[snip]
> Is this something that I should look into or is it no
doe sit include monitoring replicas ?
Cheers,
Ben-
On Fri, Oct 7, 2011 at 3:01 PM, Fernando Hevia wrote:
> pgwatch might also be worth taking a look
> at: http://www.cybertec.at/en/postgresql_products/pgwatch-cybertec-enterprise-postgresql-monitor
> Fernando.-
> On Fri, Sep 30, 2
Hello,
Has any performance or evaluation done for pg9.x streaming replication
over WAN ?
How adequate is the protocol to push WALs over long distance ?
Any best practice tuning wal_* for WAN ?
Cheers,
Ben-
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
On Oct 3, 2011, at 6:52 AM, 姜头 wrote:
> How can i get record by data block not by sql?
>
> I want to read and write lots of data by data blocks and write record to a
> appointed data block and read it.
> so i can form a disk-resident tree by recording the block address. But i
> don't know how
On Sep 30, 2011, at 12:07 PM, bricklen wrote:
> I've been informed that this type of operation is called "symmetric
> difference"[1], and can be represented by A ∆ B. A couple of
> alternative names were proposed, "array_symmetric_difference" and
> "array_xor".
> Does anyone have a preference fo
On Sep 27, 2011, at 6:37 PM, Craig Ringer wrote:
> On 09/27/2011 12:54 PM, Ben Chobot wrote:
>>
>> My memory is fuzzy but as I recall, a possible downside to using
>> deferred constraints was increased memory usage
>
> That's right. PostgreSQL doesn't
On Sep 26, 2011, at 10:52 AM, Maria L. Wilson wrote:
> Our first try to solve this problem has been to convert these triggers into a
> constraint trigger which allows for DEFERRABLE INITIALLY DEFERRED flags.
> This, we are finding, is forcing the trigger function to run after the
> triggering
On May 9, 2011, at 1:32 PM, Chris Hoover wrote:
> 1.3 TB Fusion IO (2 1.3 TB Fusion IO Duo cards in a raid 10)
Be careful here. What if the entire card hiccups, instead of just a device on
it? (We've had that happen to us before.) Depending on how you've done your
raid 10, either all your parit
On Apr 29, 2011, at 7:24 AM, Mark Steben wrote:
> Hi,
> Had a recent conversation with a tech from this company called FUSION-IO.
> They sell
> io cards designed to replace conventional disks. The cards can be up to 3
> TB in size and apparently
> are installed in closer proximity to the CPU
Hey!
I'm having some trouble optimizing a query that uses a custom operator class.
#Postgres has given me a solution for natural sort -
http://www.rhodiumtoad.org.uk/junk/naturalsort.sql
I'm trying to run it over a huge table - when running it on demand,
the data needs to be dumped to memory and s
On Feb 3, 2011, at 1:50 PM, Mladen Gogala wrote:
> So, I will have to go back on my decision to use Postgres and re-consider
> MySQL? I will rather throw away the effort invested in studying Postgres than
> to risk an unfixable application downtime. I am not sure about the world
> domination t
On Jan 4, 2011, at 8:48 AM, Merlin Moncure wrote:
>
> most flash drives, especially mlc flash, use huge blocks anyways on
> physical level. the numbers claimed here
> (http://www.fusionio.com/products/iodrive/) (141k write iops) are
> simply not believable without write buffering. i didn't se
On Dec 23, 2010, at 11:58 AM, Andy wrote:
>
> Somewhat tangential to the current topics, I've heard that FusionIO uses
> internal cache and hence is not crash-safe, and if the cache is turned off
> performance will take a big hit. Is that your experience?
It does use an internal cache, but it
On Dec 23, 2010, at 12:52 PM, Tom Lane wrote:
> Ben writes:
>> i have a schema similar to the following
>
>> create index foo_s_idx on foo using btree (s);
>> create index foo_e_idx on foo using btree (e);
>
>> i want to do queries like
>
>> s
like enable_seqscan and such
which force the use of bitmap AND, and i don't know how to tell the query
planner about the structure of the data (i don't think this is adequately
captured in any of the statistics it generates, i would need multi-column
statistics.)
any clues?
best rega
--
Michael Ben-Nes - Internet Consultant and Director.
http://www.epoch.co.il - weaving the Net.
Cellular: 054-4848113
--
On Tue, Dec 21, 2010 at 11:07 PM, Merlin Moncure wrote:
> On Tue, Dec 21, 2010 at 10:50 AM, Pavel Stehule
> wrote:
>
rds
>
> Pavel Stehule
>
> 2010/12/21 Michael Ben-Nes :
> > Hi,
> >
> > Just stumbled on the following post:
> >
> http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
> >
> > The post claim that MySQL can do more qps the
, is it possbile to achive the same using PG 9.0.x
2. Is it possible at all?
It seems to me that if such gain is possible, PG should benefit from that
significantly when it comes to Key/Value queries.
Best,
Miki
--
Michael Ben-Nes - Internet
On Nov 12, 2010, at 8:14 AM, Kyriacos Kyriacou wrote:
> We are still using PostgreSQL 8.2.4. We are running a 24x7 system and
> database size is over 200Gb so upgrade is not an easy decision!
This is why we have slony, so you can slowly upgrade your 200Gb while you're
live and then only suffer
< '2009-10-21';
give different answers, despite being equivalent, but i understand it is hard
to push things into subqueries in general. in this case it is only legal
because we partition by ts.
thanks again for the explanations!
best, ben
--
Sent via pgsql
ct that the query planner would be able to choose
either of those plans. this is important -- with the real data i'm working
with, the table is very large, and the sequential scan is a killer.
are these queries equivalent, or am i mistaken? if the planner distinguishes
between these plan
On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote:
>> is my intuition completely off on this?
>>
>> best regards, ben
>>
>
> If your SELECT retrieves substantial amount of records, table scan could
> be more efficient than index access.
>
> Now, if while re
> On Thu, 2010-10-28 at 12:59 -0700, Ben wrote:
>> On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
>>>
>>> My tests show you are incorrect:
>>>
>>>
>>> part_test=# explain analyze select * from foo join bar using (i) where
>>>
On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
>
> My tests show you are incorrect:
>
>
> part_test=# explain analyze select * from foo join bar using (i) where
> i=9;
>QUERY
> PLAN
> -
On Oct 28, 2010, at 11:50 AM, Joshua D. Drake wrote:
>>> Yes the constraints have to be static. Not sure about the operator
>>> question honestly.
>>
>> this seems to severely restrict their usefulness -- our queries are data
>> warehouse analytical -type queries, so the constraints are usually
lly want to look into expression indexes, not clustered
> ones.
what would expression indexes give me?
thanks and best regards, ben
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
imple operators like >,
< which basically forces btree indexes when i want to use gist) it is indeed
likely that partitioning can be slower than one big table with a clustered
index.
is my intuition completely off on this?
best regards, ben
--
Sent via pgsql-performance maili
On Oct 7, 2010, at 4:38 PM, Steve Crawford wrote:
> I'm weighing options for a new server. In addition to PostgreSQL, this
> machine will handle some modest Samba and Rsync load.
>
> I will have enough RAM so the virtually all disk-read activity will be
> cached. The average PostgreSQL read act
On Jul 24, 2010, at 12:20 AM, Yeb Havinga wrote:
> The problem in this scenario is that even when the SSD would show not data
> loss and the rotating disk would for a few times, a dozen tests without
> failure isn't actually proof that the drive can write it's complete buffer to
> disk after po
On Jul 15, 2010, at 8:16 PM, Scott Carey wrote:
> On Jul 15, 2010, at 12:35 PM, Ben Chobot wrote:
>
>> On Jul 15, 2010, at 9:30 AM, Scott Carey wrote:
>>
>>>> Many raid controllers are smart enough to always turn off write caching on
>>>> the drives,
On Jul 15, 2010, at 2:40 PM, Ryan Wexler wrote:
> On Thu, Jul 15, 2010 at 12:35 PM, Ben Chobot wrote:
> On Jul 15, 2010, at 9:30 AM, Scott Carey wrote:
>
> >> Many raid controllers are smart enough to always turn off write caching on
> >> the drives, and also disab
On Jul 15, 2010, at 9:30 AM, Scott Carey wrote:
>> Many raid controllers are smart enough to always turn off write caching on
>> the drives, and also disable the feature on their own buffer without a BBU.
>> Add a BBU, and the cache on the controller starts getting used, but *not*
>> the cache
On Jul 15, 2010, at 12:40 PM, Ryan Wexler wrote:
> On Wed, Jul 14, 2010 at 7:50 PM, Ben Chobot wrote:
> On Jul 14, 2010, at 6:57 PM, Scott Carey wrote:
>
> > But none of this explains why a 4-disk raid 10 is slower than a 1 disk
> > system. If there is no write-back ca
On Jul 14, 2010, at 6:57 PM, Scott Carey wrote:
> But none of this explains why a 4-disk raid 10 is slower than a 1 disk
> system. If there is no write-back caching on the RAID, it should still be
> similar to the one disk setup.
Many raid controllers are smart enough to always turn off write
On Jul 8, 2010, at 12:37 PM, Ryan Wexler wrote:
> One thing I don't understand is why BBU will result in a huge performance
> gain. I thought BBU was all about power failures?
When you have a working BBU, the raid card can safely do write caching. Without
it, many raid cards are good about tur
On Jun 30, 2010, at 11:12 AM, t...@exquisiteimages.com wrote:
> I read a post
> earlier today that mentioned in passing that it was better to have a
> faster processor than more cores.
This really depends on your workload and how much you value latency vs.
throughput. If you tend to have a lot
On May 24, 2010, at 4:25 AM, Konrad Garus wrote:
> Do shared_buffers duplicate contents of OS page cache? If so, how do I
> know if 25% RAM is the right value for me? Actually it would not seem
> to be true - the less redundancy the better.
You can look into the pg_buffercache contrib module.
>
I've got a sql language function which does a fairly simple select from a
table. If I give it one value, it performs quickly (half a ms). If I give it
another value, it does not (1.1 seconds). When I run the equivalent select
outside of the function, both values perform roughly the same (even th
On Mar 17, 2010, at 7:41 AM, Brad Nicholson wrote:
> As an aside, some folks in our Systems Engineering department here did
> do some testing of FusionIO, and they found that the helper daemons were
> inefficient and placed a fair amount of load on the server. That might
> be something to watch o
On Mar 16, 2010, at 6:04 PM, Rob Wultsch wrote:
> Lets say I have a large table bigTable to which I would like to add
> two btree indexes. Is there a more efficient way to create indexes
> than:
> CREATE INDEX idx_foo on bigTable (foo);
> CREATE INDEX idx_baz on bigTable (baz);
> Or
> CREATE INDEX
Autovacuum is your friend for minimal downtime. It is configurable to let you
adjust how invasive it will be, and you can have different settings per table
if you wish.
As for the reindex, why do you think you will be reindexing regularly?
On Mar 15, 2010, at 10:30 PM, Meena_Ramkumar wrote:
>
On Mar 10, 2010, at 6:22 PM, Paul McGarry wrote:
> Hi there,
>
> I'm after a little bit of advice on the shared_buffers setting (I have
> read the various docs on/linked from the performance tuning wiki page,
> some very helpful stuff there so thanks to those people).
>
> I am setting up a 64bit
On Mar 8, 2010, at 12:50 PM, Greg Smith wrote:
> Ben Chobot wrote:
>> We've enjoyed our FusionIO drives very much. They can do 100k iops without
>> breaking a sweat. Just make sure you shut them down cleanly - it can up to
>> 30 minutes per card to recover f
We've enjoyed our FusionIO drives very much. They can do 100k iops without
breaking a sweat. Just make sure you shut them down cleanly - it can up to 30
minutes per card to recover from a crash/plug pull test.
I also have serious questions about their longevity and failure mode when the
flash
On Feb 27, 2010, at 2:29 PM, Chris wrote:
> Hi, I'm having an issue where a postgres process is taking too much
> memory when performing many consecutive inserts and updates from a PHP
[snip]
In your postgresql.conf file, what are the settings for work_mem and
shared_buffers?
On Feb 26, 2010, at 11:23 AM, Tory M Blue wrote:
> On Fri, Feb 26, 2010 at 5:09 AM, Kevin Grittner
> wrote:
>> Tory M Blue wrote:
>>
>>> 2010-02-25 22:53:13 PST LOG: checkpoint starting: time
>>> 2010-02-25 22:53:17 PST postgres postgres [local] LOG: unexpected
>>> EOF on client connection
>>>
On Feb 23, 2010, at 2:23 PM, Kevin Grittner wrote:
>
> Here are the values from our two largest and busiest systems (where
> we found the pg_xlog placement to matter so much). It looks to me
> like a more aggressive bgwriter would help, yes?
>
> cir=> select * from pg_stat_bgwriter ;
> -[ RECOR
On Feb 15, 2010, at 1:25 PM, Rose Zhou wrote:
> Thanks Ben:
>
> I will adjust the auto vacuum parameters. It is on now, maybe not frequently
> enough.
> How to get the disk space back to OS? Will a Vacuum Full Verbose get the disk
> space back to OS?
>
>
Yes,
On Feb 16, 2010, at 1:29 PM, Ben Chobot wrote:
> I'm having problems with another one of my queries after moving from 8.1.19
> to 8.4.2. On 8.1.19, the plan looked like this:
>
> http://wood.silentmedia.com/bench/8119
>
> That runs pretty well. On 8.4.2, the sam
I'm having problems with another one of my queries after moving from 8.1.19 to
8.4.2. On 8.1.19, the plan looked like this:
http://wood.silentmedia.com/bench/8119
That runs pretty well. On 8.4.2, the same query looks like this:
http://wood.silentmedia.com/bench/842_bad
If I turn off mergejoin
On Feb 15, 2010, at 11:59 AM, Rose Zhou wrote:
> Good day,
>
> I have a PostgreSQL 8.4 database installed on WinXP x64 with very heavy
> writing and updating on a partitioned table. Sometimes within one minute,
> there are tens of file with size=1,048,576kb (such as
> filenode.1,filenode.2,...fil
Awesome, that did the trick. Thanks Tom! So I understand better, why is my case
not the normal, better case?
(I assume the long-term fix is post-9.0, right?)
On Feb 15, 2010, at 9:26 AM, Tom Lane wrote:
> Ben Chobot writes:
>> On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:
>
On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:
> Could you show the query, along with table definitions (including
> indexes)?
Oh, yeah, I suppose that would help. :)
http://wood.silentmedia.com/bench/query_and_definitions
(I'd paste them here for posterity but I speculate the reason my fir
(Apologies if this ends up coming through multiple times - my first attempts
seem to have gotten stuck.)
We recently took the much needed step in moving from 8.1.19 to 8.4.2. We took
the downtime opportunity to also massively upgrade our hardware. Overall, this
has been the major improvement yo
We're looking to upgrade our database hardware so that it can sustain
us while we re-architect some of the more fundamental issues with our
applications. The first thing to spend money on is usually disks, but
our database currently lives almost entirely on flash storage, so
that's already
#x27;m wrapping about 1500 inserts in a transaction block. Since
its an I/O bottlenecks, COPY statements might not give me much advantage.
Its definitely a work in progress :)
Ben
On 09/12/2009 5:31 AM, Andy Colson wrote:
On 12/07/2009 12:12 PM, Ben Brehmer wrote:
Hello All,
I'm in the
input connection' I mean "psql -U postgres -d dbname
-f one_of_many_sql_files".
Thanks,
Ben
On 07/12/2009 12:59 PM, Greg Smith wrote:
Ben Brehmer wrote:
By "Loading data" I am implying: "psql -U postgres -d somedatabase -f
sql_file.sql". The sql_file.sql conta
here
were any disk options in Amazon?
Thanks!
Ben
On 07/12/2009 10:39 AM, Thom Brown wrote:
2009/12/7 Kevin Grittner <mailto:kevin.gritt...@wicourts.gov>>
Ben Brehmer mailto:benbreh...@gmail.com>>
wrote:
> -7.5 GB memory
> -4 EC2 Compute Units (2 v
Kevin,
This is running on on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-44)
Ben
On 07/12/2009 10:33 AM, Kevin Grittner wrote:
Ben Brehmer wrote:
-7.5 GB memory
-4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units
each)
-64-bit
is
completely dedicated to the load.
Any advice would be greatly appreciated.
Thanks,
Ben
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Nov 24, 2009, at 9:23 AM, Matthew Wakeling wrote:
We're about to purchase a new server to store some of our old
databases, and I was wondering if someone could advise me on a RAID
card. We want to make a 6-drive SATA RAID array out of 2TB drives,
and it will be RAID 5 or 6 because there
Scott Otis wrote:
I am seeking advice on what the best setup for the following would be.
My company provides a hosted web calendaring solution for school
districts. For each school district we have a separate database.
Each database has 57 tables.
Over the next couple of months
Robert Haas wrote:
On Mon, Aug 10, 2009 at 11:19 AM, Kevin Grittner
wrote:
(2) Somehow use effective_cache_size in combination with some sort of
current activity metrics to dynamically adjust random access costs.
(I know, that one's total hand-waving, but it seems to have some
possibility of b
Krade wrote:
SELECT * FROM a WHERE comment_tsv @@ plainto_tsquery('love') ORDER BY
timestamp DESC LIMIT 24 OFFSET 0;
Have you tried make the full-text condition in a subselect with "offset
0" to stop the plan reordering?
eg:
select *
from (
select * from a where comment_tsv @@ plainto_tsq
Marc Cousin wrote:
Le Thursday 16 July 2009 22:07:25, Kevin Grittner a écrit :
Marc Cousin wrote:
the hot parts of these 2 tables are extremely likely to be in the
database or linux cache (buffer hit rate was 97% in the example
provided). Moreover, the first two queries of the insert procedure
Marc Cousin wrote:
This mail contains the asked plans :
Plan 1
around 1 million records to insert, seq_page_cost 1, random_page_cost 4
-> Hash (cost=425486.72..425486.72 rows=16746972 width=92) (actual
time=23184.196..23184.196 rows=16732049 loops=1)
-> Seq Scan on
On Mon, 20 Apr 2009, da...@lang.hm wrote:
one huge advantage of putting the sql into the configuration is the ability
to work around other users of the database.
+1 on this. We've always found tools much easier to work with when they
could be adapted to our schema, as opposed to changing our
On Wed, 11 Feb 2009, Scott Carey wrote:
On a large partitioned database, ordinary vacuum is a very very difficult
option.
Most of the time on such a system, most tables are dormant with respect to
writes and never need to be vacuumed. A 'daily vacuum' is likely to take a
full day to run on
On Feb 11, 2009, at 6:57 AM, Rajesh Kumar Mallah wrote:
why is it not a good idea to give end users control over when they
want to run it ?
There's nothing stopping you from just turning off autovacuum and
running vacuum manually. The point of autovacuum is to vacuum "as
needed."
--
Sen
On Tue, 9 Dec 2008, Robert Haas wrote:
I don't believe the thesis. The gap between disk speeds and memory
speeds may narrow over time, but I doubt it's likely to disappear
altogether any time soon, and certainly not for all users.
I think the "not for all users" is the critical part. In 2 yea
Have a look at the pg_stat_user_tables table.
On Fri, 11 Jul 2008, Campbell, Lance wrote:
PostgreSQL: 8.2
How can I identify how many inserts and updates are being done in a
given time frame for a database?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Pub
On Fri, 16 May 2008, Scott Marlowe wrote:
Well, I'm guessing that you aren't in locale=C and that the text
Correct, I am not. And my understanding is that by moving to the C locale,
I would loose utf8 validation, so I don't want to go there. Though, it's
news to me that I would get any kind
On Fri, 16 May 2008, Scott Marlowe wrote:
Just for giggles, try running the query like so:
set enable_nestloop = off;
explain analyze ...
and see what happens. I'm guessing that the nested loops are bad choices here.
You guess correctly, sir! Doing so shaves 3 orders of magnitude off the
r
I've inherited an Oracle database that I'm porting to Postgres, and this
has been going quite well until now. Unfortunately, I've found one view (a
largish left join) that runs several orders of magnitude slower on
Postgres than it did on Oracle.
=> select version();
Have you tried the archive search tool for this very mailing list?
There's a wealth of information imparted all the time, and tuning is
generally about knowledge of what's happening, not blindly following
the output of some program.
On Mar 17, 2008, at 8:46 AM, sathiya psql wrote:
i thou
the Bitmap Heap
Scan on product it estimate 455 rows and retrieve 22248 rows.
I increased the statistics of the field which the
product_product_keywords_vector index is built on by doing:
ALTER TABLE product ALTER COLUMN product_keywords_vector SET STATISTICS
1000;
ANALYZE;
REINDEX INDEX product_product_keywords_vector;
But it didn't change a thing.
Any ideas?
Thanks in advance,
Yonatan Ben-Nes
It would probably help you to spend some time browsing the archives of
this list for questions similar to yours - you'll find quite a lot of
consistent answers. In general, you'll find that:
- If you can fit your entire database into memory, you'll get the best
performance.
- If you cannot (
You may find this informative:
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
On Mon, 22 Oct 2007, Lee Keel wrote:
I have a client server that is dedicated to being a Postgres 8.2.4 database
server for many websites. This server will contain approximately 15
databases each co
On Thu, 4 Oct 2007, Tom Lane wrote:
There's some limited smarts in there about deciding that leading columns
of an index don't matter to the sort ordering if they're constrained to
just one value by the query. But it doesn't catch the case you need,
which is that columns of an ORDER BY reques
On Thu, 4 Oct 2007, Simon Riggs wrote:
In the first query, Postgres cannot use the index because the sort order
of the index does not match the sort order of the query. When you change
the sort order of the query so that it matches that of the index, then
the index is used.
If you define your i
On Thu, 4 Oct 2007, Tom Lane wrote:
You're being about as clear as mud here, except that you obviously lied
about what you were doing in your first message. If you have a planner
problem, show us the *exact* query, the *exact* table definition, and
unfaked EXPLAIN ANALYZE output.
I didn't rea
1 - 100 of 145 matches
Mail list logo
