On 5 March 2011 12:59, Mark Thornton wrote:
> If your partitions a loosely time based and you don't want to discard old
> data, then surely the number of partitions will grow without limit.
True, but is it relevant? With monthly table partitioning it takes
hundreds of years before having "thousa
Sorry for not responding directly to your question and for changing
the subject ... ;-)
On 4 March 2011 18:18, Landreville wrote:
> That is partitioned into about 3000 tables by the switchport_id (FK to
> a lookup table), each table has about 30 000 rows currently (a row is
> inserted every 5 min
2011/2/11 Vitalii Tymchyshyn :
>> My idea as well, though it looks ugly and it would be a maintenance
>> head-ache (upgrading the index as new transaction types are added
>> would mean "costly" write locks on the table,
>
> Create new one concurrently.
Concurrently? Are there any ways to add larg
2011/2/11 Віталій Тимчишин :
> If the list is hard-coded, you can create partial index on
> account_transaction(account_id, created desc) where trans_type_id in ( ...
> long, hard-coded list ...)
My idea as well, though it looks ugly and it would be a maintenance
head-ache (upgrading the index as
On 4 February 2011 04:46, Josh Berkus wrote:
> "Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues. We would rather have the
> problems reported and fixed. We have discussed a more sophisticated
> system of per-class cost adjustment ins
[Greg Smith]
> Here's the comment from that describing the main technique used to fix it:
>
> "This module tries to replace MIN/MAX aggregate functions by subqueries of
> the form
>
> (SELECT col FROM tab WHERE ... ORDER BY col ASC/DESC LIMIT 1)
Huh ... that sounds a bit like pg 8.0 to me ;-) I r
I implemented table partitioning, and it caused havoc with a "select
max(id)" on the parent table - the query plan has changed from a
lightningly fast backwards index scan to a deadly seq scan. Both
partitions are set up with primary key index and draws new IDs from
the same sequence ... "select m
Just a general note re the subject, I've also had troubles with
postgres being unable to optimize a query with OR. The work-around,
although a bit messy, was to use a UNION-query instead.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscr
[Tom Lane]
> EXPLAIN ANALYZE doesn't account for all of the runtime involved. In
> this case, I'd bet that session startup/shutdown is a big part of the
> difference.
The session startup/shutdown should be the same for the real SQL and
the broken SQL, shouldn't it?
[Artur Zając]
> time psql -c
On 29 September 2010 10:03, Mark Kirkwood
> Yeah, I think the idea of trying to
have a few smaller indexes for the 'hot'
> customers is a good idea. However I am wondering if just using single column
> indexes and seeing if the bitmap scan/merge of smaller indexes is actually
> more efficient is w
I just got this crazy, stupid or maybe genius idea :-)
One thing that I've learned in this thread is that fat indexes (i.e.
some index on some_table(a,b,c,d,e,f)) is to be avoided as much as
possible.
One of our biggest indexes looks like this:
acc_trans(customer_id, trans_type, created)
For th
On 25 September 2010 00:00, Greg Smith wrote:
> Overindexed tables containing more columns than are actually selective is a
> very popular source of PostgreSQL slowdowns. It's easy to say "oh, I look
> this data up using columns a,b,c, so lets put an index on a,b,c". But if an
> index on a alone
Thanks for spending your time on this ... amidst all the useful
feedback I've received, I'd rate your post as the most useful post.
>> 1) Are there any good ways to verify my hypothesis?
>
> You can confim easily whether the contents of the PostgreSQL buffer cache
> contain when you think they do
On 24 September 2010 21:24, Brad Nicholson wrote:
>> The pertinent difference between pg_stat_user_indexes and
>> pg_statio_user_indexes is the latter shows the number of blocks read from
>> disk or found in the cache.
>
> I have a minor, but very important correction involving this point. The
>
On 24 September 2010 00:12, Mark Kirkwood wrote:
> Re index size, you could try indexes like:
>
> some_table(a)
> some_table(b)
>
> which may occupy less space, and the optimizer can bitmap and/or them to
> work like the compound index some_table(a,b).
Hm ... never considered that ... but is it c
On 24 September 2010 21:06, Bob Lunney wrote:
> First off, what version of PostgreSQL are you running? If you have 8.4,
> nightly vacuuming shouldn't be necessary with properly tuned autovacuum jobs.
8.3. We'll upgrade to 9.0 during the December holidays fwiw. But
point taken, I will continue
On 24 September 2010 19:16, Brad Nicholson wrote:
[Brad Nicholson]
> Why is the vacuum dragging out over time? Is the size of your data
> increasing, are you doing more writes that leave dead tuples, or are your
> tables and/or indexes getting bloated?
Digressing a bit here ... but the biggest r
On 24 September 2010 00:12, Mark Kirkwood wrote:
> All good questions! Before (or maybe as well as) looking at index sizes vs
> memory I'd check to see if any of your commonly run queries have suddenly
> started to use different plans due to data growth, e.g:
>
> - index scan to seq scan (perhaps
On 24 September 2010 18:23, Bob Lunney wrote:
> Consult pg_statio_user_indexes to see which indexes have been used
> and how much.
What is the main differences between pg_statio_user_indexes and
pg_stat_user_indexes?
> Indexes with comparitively low usages rates aren't helping you much and are
On 23 September 2010 22:55, Kevin Grittner wrote:
> Have you turned on checkpoint logging?
Yes ... it seems so:
13:19:13.840 - LOG: checkpoint complete: wrote 3849 buffers (0.2%); 0
transaction log file(s) added, 0 removed, 5 recycled; write=269.551 s,
sync=0.103 s, total=269.953 s
13:19:13.841
We've come to a tipping point with one of our database servers, it's
generally quite loaded but up until recently it was handling the load
well - but now we're seeing that it struggles to process all the
selects fast enough. Sometimes we're observing some weird lock-like
behaviour (see my other po
On 15 September 2010 12:05, Tobias Brox wrote:
> Recently we've frequently encountered issues where some simple selects
> (meaning, selects doing an index lookup and fetching one row) have
> become stuck for several minutes. Apparently all requests on one
> exact table gets st
On 15 September 2010 21:28, Greg Smith wrote:
> There are some useful examples of lock views on the wiki:
>
> http://wiki.postgresql.org/wiki/Lock_Monitoring
> http://wiki.postgresql.org/wiki/Lock_dependency_information
> http://wiki.postgresql.org/wiki/Find_Locks
Thanks. I think those pages pro
On 15 September 2010 15:39, Tom Lane wrote:
> An exclusive lock will block selects too. Have you looked into pg_locks
> for ungranted lock requests?
Well - I thought so, we have a logging script that logs the content of
the pg_locks table, it didn't log anything interesting but it may be a
probl
We have a production database server ... it's quite busy but usually
working completely fine, simple queries taking a fraction of a
millisecond to run.
Recently we've frequently encountered issues where some simple selects
(meaning, selects doing an index lookup and fetching one row) have
become s
[Erik Jones]
> Right. Without the xlog directory you'll have very little chance of
> ever doing any kind of clean stop/start of your database. If you
> don't need the reliability offered by Postgres's use of transaction
> logs you'll probably be much better served with a different database
[Arjen van der Meijden]
> Your SAN-pusher should have a look at the HP-submissions for TPC-C...
> The recent Xeon systems are all without SAN's and still able to connect
> hundreds of SAS-disks.
Yes, I had a feeling that the various alternative solutions for "direct
connection" hadn't been inves
[Peter Koczan - Wed at 10:56:54AM -0600]
> We're considering setting up a SAN where I work. Is there anyone using
> a SAN, for postgres or other purposes? If so I have a few questions
> for you.
Some time ago, my boss was planning to order more hardware - including a
SAN - and coincidentally, SANs
[EMAIL PROTECTED]
> The table was quite huge (say 20k of products along with detailed
> descriptions etc.) and was completely updated and about 12x each day, i.e.
> it qrew to about 12x the original size (and 11/12 of the rows were dead).
> This caused a serious slowdown of the application each day
[Gábor Farkas - Fri at 10:40:43AM +0100]
> my question is: is it recommended to use it? or in other words, should i
> only use autovacuum? or it's better to use manual-vacuuming? which one
> is the "way of the future" :) ? or should i use both auto-vacuum and
> manual-vacuum?
Nightly vacuums ar
[Decibel! - Tue at 06:07:44PM -0500]
> It's still in the same chassis, though, which means if you lose memory
> or mobo you're still screwed. In a SAN setup for redundancy, there's
> very little in the way of a single point of failure; generally only the
> backplane, and because there's very little
We're also considering to install postgres on SAN - that is, my boss is
convinced this is the right way to go.
Advantages:
1. Higher I/O (at least the salesman claims so)
2. Easier to upgrade the disk capacity
3. Easy to set up "warm standby" functionality. (Then again, if the
postgres serve
[Sachchida Ojha - Wed at 04:40:09PM -0400]
> I see some long running transaction in my pg_activity_log table. My app
> becomes almost unusable. My question is
> How can I query the database to see what sql these transactions are
> running.
" in transaction" means that no sql query is running at
[Christo Du Preez - Wed at 12:25:20PM +0200]
> Is there some kind of performance testing utility available for
> postgresql Something I can run after installing postgresql to help me
> identify if my installation is optimal.
>
> I've been battling for days now trying to sort out performance issues
[Greg Smith - Fri at 12:53:55AM -0400]
> Munin is a very interesting solution to this class of problem. They've
> managed to streamline the whole data collection process by layering clever
> Perl hacks three deep. It's like the anti-SNMP--just build the simplest
> possible interface that will
[Alexander Staubo - Thu at 04:52:55PM +0200]
> I have been considering tarring them up as a proper release at some
> point. Anyone interested?
Yes.
Eventually I have my own collection as well:
db_activity - counts the number of (all, slow, very slow, stuck "idle in
transaction") queries in prog
We had problems again, caused by long running transactions. I'm
monitoring the pg_stat_activity view, checking the query_start of all
requests that are not idle - but this one slipped under the radar as the
application was running frequent queries towards the database.
That's not what concerns me
[Erik Jones - Wed at 09:31:48AM -0500]
> I use cacti (http://cacti.net) which does the same thing that munin
> does but in php instead. Here's what I use to db stats to it (again,
> php):
I haven't tried cacti, but our sysadm has done a little bit of research
and concluded "cacti is better".
I have my postgres munin monitoring script at
http://oppetid.no/~tobixen/pg_activity.munin.txt (had to suffix it with
.txt to make the local apache happy).
I would like to see what others have done as well.
---(end of broadcast)---
TIP 1: if postin
[Chad Wagner - Tue at 08:24:34AM -0500]
> I guess go with your gut, but at some point the expressions are going to be
> too complicated to maintain, and inefficient.
The layout of my system is quite flexible, so it should eventually be
fairly trivial to throw in a date dimension at a later stage.
Does anyone have experience with using postgres for data warehousing?
Right, I saw one post suggestion to use mysql for a mostly read-only
database ... but anyway, I think it's not a question to change the
database platform for this project, at least not today ;-)
Ralph Kimball seems to be some ki
[Heikki Linnakangas - Fri at 10:41:34AM +]
> I thought about partitioning the table by state, putting rows with
> state=4 into one partition, and all others to another partition.
That sounds like a good idea - but wouldn't that be costly when changing state?
---(end o
[Richard Huxton - Fri at 09:17:48AM +]
> Try a partial index:
> CREATE INDEX my_new_index ON events (event_time)
> WHERE state in (1,2,3);
I have that, the index is used and the query is lightning fast - the
only problem is that the planner is using the wrong estimates. This
becomes a real pr
[Peter Childs - Fri at 08:56:54AM +]
> Can you say what state might be rather than what it is not. I'm guess
> that state is an int but there is only a limited list of possible
> states, if you can say what it might be rather than what it is the
> index is more liklly to be used.
explain sel
We have a table with a timestamp attribute (event_time) and a state flag
which usually changes value around the event_time (it goes to 4). Now
we have more than two years of events in the database, and around 5k of
future events.
It is important to frequently pick out "overdue events", say:
se
[Daryl Herzmann - Sat at 12:59:03PM -0600]
> As the months have gone by, I notice many of my tables having *lots* of
> unused item pointers. For example,
Probably not the issue here, but we had some similar issue where we had
many long-running transactions - i.e. some careless colleague entering
[Tom Lane - Tue at 10:29:53PM -0500]
> These are not actually exactly the same thing. In particular, I suppose
> your table contains a lot of nulls?
Yes; I'm sorry I was a bit quick with the first posting.
---(end of broadcast)---
TIP 1: if posting
[Tobias Brox - Wed at 04:01:56AM +0100]
> We're using 8.1 - I thought such a construct was safe in pg 8.1:
>
> select max(indexed_value) from huge_table;
>
> while earlier we had to use:
>
> select indexed_value from huge_table order by indexed_value desc limit 1;
&
We're using 8.1 - I thought such a construct was safe in pg 8.1:
select max(indexed_value) from huge_table;
while earlier we had to use:
select indexed_value from huge_table order by indexed_value desc limit 1;
seems like I was wrong:
mydb=> explain analyze select indexed_value1 from mytabl
[Chris - Fri at 02:32:05PM +1100]
> Not really. A bad query is a bad query (eg missing a join element). It
> won't show up for 3000 rows, but will very quickly if you increase that
> by a reasonable amount. Even as simple as a missing index on a join
> column won't show up for a small dataset bu
[EMAIL PROTECTED] - Thu at 06:37:12PM -0600]
> As my dataset has gotten larger I have had to throw more metal at the
> problem, but I have also had to rethink my table and query design. Just
> because your data set grows linearly does NOT mean that the performance of
> your query is guaranteed to
[Jeff Davis - Thu at 04:57:54PM -0800]
> > We're having the same issues, so we do the dumping and restoring every
> > now and then to be sure everything is properly cleaned up. With 8.1.
> >
>
> What's causing that? Is it index bloat?
>
> I would think a REINDEX would avoid having to dump/resto
[Paul Lathrop - Thu at 02:59:27PM -0800]
> growing disk space usage. The DBA had come to the conclusion that the
> VACUUM command did/does not work on these systems, because even after a
> VACUUM FULL, the size of the database was continually increasing. So, as
> things stand with the PG7.2 machine
[Madison Kelly - Mon at 08:48:19AM -0500]
> Ah, sorry, long single queries is what you meant.
No - long running single transactions :-) If it's only read-only
queries, one will probably benefit by having one transaction for every
query.
---(end of broadcast)
[Madison Kelly - Mon at 08:10:12AM -0500]
> to run, which puts it into your "drawback" section. The server in
> question is also almost under load of some sort, too.
>
> A great tip and one I am sure to make use of later, thanks!
I must have been sleepy, listing up "cons" vs "drawbacks" ;-)
Any
[Madison Kelly - Thu at 10:25:07AM -0500]
> Will the priority of the script pass down to the pgsql queries it calls?
> I figured (likely incorrectly) that because the queries were executed by
> the psql server the queries ran with the server's priority.
I think you are right, and in any case, I
[Jim C. Nasby - Thu at 11:31:26AM -0500]
> The issue with pg_xlog is you don't need bandwidth... you need super-low
> latency. The best way to accomplish that is to get a battery-backed RAID
> controller that you can enable write caching on. In fact, if the
> controller is good enough, you can theo
[Ron - Thu at 03:10:35PM -0400]
> Jim is correct that traditional 7.x folklore regarding shared buffer
> size is nowhere near as valid for 8.x. Jim tends to know what he is
> talking about when speaking about pg operational issues.
I would not doubt it, but it's always better to hear it from mo
[Jim C. Nasby - Thu at 12:00:39PM -0500]
> Well, if you're buying unreliable hardware, there's not much you can
> do... you're setting yourself up for problems.
I'm luckily not responsible for the hardware, but my general experience
tells that you never know anything about hardware reliability unt
[Jim C. Nasby - Thu at 12:00:39PM -0500]
> What's reasonable for work_mem depends on your workload. If you've got
> some reporting queries that you know aren't run very concurrently they
> might benefit from large values of work_mem. For stats.distributed.net,
> I set work_mem to something like 2MB
[Jim C. Nasby - Thu at 11:45:32AM -0500]
> > > The issue with pg_xlog is you don't need bandwidth... you need super-low
> > > latency. The best way to accomplish that is to get a battery-backed RAID
> > > controller that you can enable write caching on.
> >
> > Sounds a bit risky to me :-)
>
> We
[Jim C. Nasby - Thu at 11:31:26AM -0500]
> Yeah, test setups are a good thing to have...
We would need to replicate the production traffic as well to do reliable
tests. Well, we'll get to that one day ...
> The issue with pg_xlog is you don't need bandwidth... you need super-low
> latency. The b
[Jim C. Nasby - Thu at 10:28:31AM -0500]
> I think it'd be much better to experiment with using much larger
> shared_buffers settings. The conventional wisdom there is from 7.x days
> when you really didn't want a large buffer, but that doesn't really
> apply with the new buffer management we got i
[Jim C. Nasby - Thu at 10:28:31AM -0500]
> I think it'd be much better to experiment with using much larger
> shared_buffers settings. The conventional wisdom there is from 7.x days
> when you really didn't want a large buffer, but that doesn't really
> apply with the new buffer management we got i
I just came to think about /proc/sys/swappiness ...
When this one is set to a high number (say, 100 - which is maximum), the
kernel will aggressively swap out all memory that is not beeing
accessed, to allow more memory for caches. For a postgres server, OS
caches are good, because postgres relie
[Matthew T. O'Connor - Sun at 10:42:34AM -0400]
> Yeah, I think if the delay settings are too high it can cause problems,
> that's part of the reason we have yet to turn these on be default since
> we won't have enough data to suggest good values. Can you tell us what
> settings you finally set
[Matthew T. O'Connor - Wed at 02:33:10PM -0400]
> In addition autovacuum respects the work of manual or cron based
> vacuums, so if you issue a vacuum right after a daily batch insert /
> update, autovacuum won't repeat the work of that manual vacuum.
I was experimenting a bit with autovacuum no
[Tom Lane - Tue at 02:26:53PM -0400]
> > autovacuum_vacuum_cost_delay = 500
> > autovacuum_vacuum_cost_limit = 200
>
> Well, that's going to cause it to sleep half a second after every dozen
> or so page I/Os. I think you'd be well advised to reduce the delay.
Modified it to 20/250, and it defin
[Tom Lane - Tue at 02:04:55PM -0400]
> > It seems stuck, has had the same transid for a long while, and the
> > number of undeletable dead rows in our tables are increasing.
>
> Perhaps you have overly aggressive vacuum cost delay settings?
Perhaps, though I wouldn't expect it to sleep in the mid
[Tom Lane - Tue at 01:18:27PM -0400]
> >> Hmph. Is the autovac process actually doing anything (strace would be
> >> revealing)?
It's definitively doing something; mostly reading, but also some few
writes, semops and opens.
> If not, can you attach to the autovac process with gdb and
> >> get a
[Tom Lane - Tue at 01:09:52PM -0400]
> Hmph. Is the autovac process actually doing anything (strace would be
> revealing)? If not, can you attach to the autovac process with gdb and
> get a stack trace to see where it's blocked?
Sorry ... I SIGINT'ed it, and now it's gone :-( I thought reloadin
[Tom Lane - Tue at 12:42:52PM -0400]
> > belonging to autovacuum ... how come?
>
> Blocked on someone else's lock, maybe?
hardly, the autovacuum is the only one having such a low transaction id,
and also the only one hanging around when waiting a bit and rechecking
the pg_locks table.
--
[Tobias Brox - Tue at 06:39:13PM +0200]
> Thanks a lot for the quick reply - I've already identified one
> long-running transaction.
belonging to autovacuum ... how come?
---(end of broadcast)---
TIP 9: In versions below 8.0, the p
[Tom Lane - Tue at 12:23:40PM -0400]
> Look in pg_locks to see the lowest-numbered transaction ID --- each
> transaction will be holding exclusive lock on its own XID. You can
> correlate that back to pg_stat_activity via the PID.
Thanks a lot for the quick reply - I've already identified one
lon
While doing a verbose vacuum, I'm constantly hitting things like:
DETAIL: 3606 dead row versions cannot be removed yet.
I believe this is a problem, because I still do have some empty tables
requireing up to 3-400 ms just to check if the table is empty (see
thread "slow queue-like empty table").
[Jim C. Nasby - Mon at 04:18:27PM -0500]
> I can agree to that, but we'll never get any progress so long as every
> time hints are brought up the response is that they're evil and should
> never be in the database. I'll also say that a very simple hinting
> language (ie: allowing you to specify acc
[Tom Lane - Wed at 04:33:54PM -0400]
> > We have indices on the users_id field and the (users_id, created)-tuple.
>
> Neither of those indexes can provide the sort order the query is asking
> for.
Ah; that's understandable - the planner have two options, to do a index
traversion without any extra
Look at this:
NBET=> explain select * from account_transaction where users_id=123456 order by
created desc limit 10;
QUERY PLAN
-
To be a bit constructive, could it be an idea to add unsubscribe
information as one of the standard tailer tips? Then unsubscribe info
wouldn't appear in every mail, but often enough for people considering
to unsubscribe. To be totally non-constructive, let me add a bit to the
noise below:
[Brun
[Csaba Nagy - Thu at 10:45:35AM +0200]
> So you should check for "idle in transaction" sessions, those are bad...
> or any other long running transaction.
Thank you (and others) for pointing this out, you certainly set us on
the right track. We did have some few unclosed transactions;
transaction
[Arnaud Lesauvage - Tue at 02:13:59PM +0200]
> Tobias Brox wrote:
> >Oh, the gid is not primary key. I guess I should also apologize for
> >adding noise here :-)
>
> Yes, it is a primary key, but I am the noise maker here ! ;-)
Oh - it is. How can you have a default v
[Tobias Brox - Tue at 02:10:04PM +0200]
> Did you try "analyze" as well? It's weird it's using seq scan, since
> you have a primary key it's supposed to have an index ... though 500
> rows is little.
>
> I just checked up our own production database, takes
[Arnaud Lesauvage - Tue at 01:25:10PM +0200]
> I have a performance problem, but I am not sure whether it really
> is a problem or not.
> QUERY PLAN
> --
[Tobias Brox - Thu at 08:56:31AM +0200]
> It really seems like some transaction is still viewing the queue, since
> it found 38k of non-removable rows ... but how do I find the pid of the
> transaction viewing the queue? As said, the pg_locks didn't give me any
> hints ...
Dropp
I have a query which really should be lightning fast (limit 1 from
index), but which isn't. I've checked the pg_locks table, there are no
locks on the table. The database is not under heavy load at the moment,
but the query seems to draw CPU power. I checked the pg_locks view, but
found nothing
I found a way to survive yet some more weeks :-)
One of the queries we've had most problems with today is principially
something like:
select A.*,sum(B.*) from A join B where A.created>x and ... order by
A.created desc limit 32 group by A.*
There is by average two rows in B for every row in
[Edoardo Ceccarelli - Wed at 06:49:23PM +0200]
> ...another thing is, how could autovacuum check for machine load, this
> is something I cannot imagine right now...
One solution I made for our application, is to check the
pg_stats_activity view. It requires some config to get the stats
available
[Edoardo Ceccarelli - Wed at 06:08:30PM +0200]
> We also activated the autovacuum feature to give it a try and that's
> were our problems started.
(...)
> How can I configure the vacuum to run after the daily batch insert/update?
I think you shouldn't use autovacuum in your case.
We haven't dare
[Scott Marlowe - Wed at 10:31:35AM -0500]
> And remember, you can always change any of those settings in session for
> just this one query to force the planner to make the right decision.
sure ... I could identify the most problematic queries, and hack up the
software application to modify the con
[Scott Marlowe - Wed at 10:19:24AM -0500]
> So, by decreasing them, you should move away from nested loops then,
> right? Has that not worked for some reason?
I want to move to nested loops, they are empirically faster in many of
our queries, and that makes sense since we've got quite big tables
[Scott Marlowe - Wed at 09:58:30AM -0500]
> Have you tried chaning the cpu_* cost options to see how they affect
> merge versus nested loop?
As said in the original post, increasing any of them shifts the planner
towards nested loops instead of merge_join. I didn't check which one of
the cost con
[Tom Lane - Tue at 06:09:56PM -0400]
> If your tables are small enough to fit (mostly) in memory, then the
> planner tends to overestimate the cost of a nestloop because it fails to
> account for cacheing effects across multiple scans of the inner table.
> This is addressed in 8.2, but in earlier v
I have some odd cases here joining two tables - the planner insists on
Merge Join, but Nested Loop is really faster - and that makes sense,
since I'm selecting just a small partition of the data available. All
planner constants seems to be set at the default values, the only way to
get a shift tow
By occation, we dropped the whole production database and refreshed it from
a database backup - and all our performance problems seems to have gone. I
suppose this means that to keep the database efficient, one eventually does
have to do reindexing and/or full vacuum from time to time?
--
Notice
[Tobias Brox - Wed at 09:22:17PM +0200]
> I'd trust linux to handle swap/cache sensibly. Eventually, become involved
> with kernel hacking ;-)
Of course, there are also some files in /proc/sys/vm that you may want to
peek into, for tuning the swapping. Particularly, at later 2.6-k
[Carlos Henrique Reimer - Wed at 03:25:15PM -0300]
> I´m trying to tune a linux box with a 12 GB database and 4 GB RAM. First
> of all I would like to stop the swapping, so the shared_buffers and sort_mem
> were decreased but even so it started swapping two hours after DBMS started
> up.
>
> I wo
[Tom Lane]
> I looked into this and (...) I've committed some changes that hopefully will
> let 8.1 be smarter about GROUP BY ... LIMIT queries.
[Mark Kirkwood]
> Very nice :-)
(...)
> This is 8.1devel from today.
Splendid :-) Unfortunately we will not be upgrading for some monthes still,
but any
[Mark Kirkwood - Fri at 03:01:01PM +1200]
> Tobias,
> Interesting example:
>
> The 'desc' seems to be the guy triggering the sort, e.g:
Oh; really an accident that I didn't notice myself, I was actually going to
remove all instances of "desc" in my simplification, but seems like I forgot.
> Howe
[Jeffrey W. Baker - Thu at 06:56:59PM -0700]
> > explain select c.id from c join b on c_id=c.id group by c.id order by c.id
> > desc limit 5;
>
> Where's b in this join clause?
"join b on c_id=c.id"
It just a funny way of writing:
select c.id from c,b where c_id=c.id group by c.id order by c.
Consider this setup - which is a gross simplification of parts of our
production system ;-)
create table c (id integer primary key);
create table b (id integer primary key, c_id integer);
create index b_on_c on b(c_id)
insert into c (select ... lots of IDs ...);
insert into b (select id
1 - 100 of 133 matches
Mail list logo
