Hi Jean-David,
On Mon, 2009-10-05 at 15:37 +0200, Jean-David Beyer wrote:
> Robert Haas wrote (in part):
>
> > Also, I'd just like to mention that vi is a much better editor than
> > emacs.
> >
> That is not my impression. I have used vi from when it first came out (I
> used ed before that) unti
> Isn't that what statement_timeout is for? Since this is entirely based
> on estimates, using arbitrary fuzzy numbers for this seems fine to me;
> precision isn't really the goal.
There's an important difference to statement_timeout: this proposal
would avoid completely taking any resources if it
On Thu, 2007-11-29 at 10:45 -0500, Tom Lane wrote:
> Given that this list spends all day every day discussing cases where the
> planner is wrong, I'd have to think that that's a bet I wouldn't take.
>
> You could probably avoid this risk by setting the cutoff at something
> like 100 or 1000 times
On Wed, 2007-11-28 at 08:54 -0500, Bill Moran wrote:
> > Nothing wrong with enable_seqscan = off except it is all or nothing type
> > of thing...
>
> If that's true, then I have a bug report to file:
[snip]
> It looks to me to be session-alterable.
I didn't mean that it can't be set per session,
On Wed, 2007-11-28 at 08:27 -0500, Bill Moran wrote:
> Is there something wrong with:
> set enable_seqscan = off
> ?
Nothing wrong with enable_seqscan = off except it is all or nothing type
of thing... if you want the big table to never use seqscan, but a medium
table which is joined in should use
On Fri, 2007-11-16 at 12:13 +0100, Tobias Brox wrote:
> [snip] should i use both auto-vacuum and
> > manual-vacuum?
I would say for 8.2 that's the best strategy (which might change with
8.3 and it's multiple vacuum workers thingy).
> That being said, we have some huge tables in our database and
On Tue, 2007-10-23 at 08:53 -0700, Ron St-Pierre wrote:
> [snip] We were running autovacuum but it interfered with
> the updates to we shut it off.
This is not directly related to your question, but it might be good for
your DB: you don't need to turn off autovacuum, you can exclude tables
indivi
> Just an idea, but with the 8.3 concurrent scan support would it be
> possible to hang a more in depth analyze over exisiting sequential
> scans. Then it would be a lower cost to have higher resolution in
> the statistics because the I/O component would be hidden.
The biggest problem with that is
On Thu, 2007-09-27 at 11:07 -0700, Ron Mayer wrote:
> Csaba Nagy wrote:
> >
> > Well, my problem was actually solved by rising the statistics target,
>
> Would it do more benefit than harm if postgres increased the
> default_statistics_target?
>
> I see a fair num
On Thu, 2007-09-27 at 10:40 -0400, Tom Lane wrote:
> And yet there's another trap here: if the parameter you passed in
> chanced to be one of the very common values, a plan that was optimized
> for a small number of matches would perform terribly.
>
> We've speculated about trying to deal with the
On Wed, 2007-09-26 at 11:22 -0400, Tom Lane wrote:
> ... how
> many values of "a" are there really, and what's the true distribution of
> counts?
table_a has 23366 distinct values. Some statistics (using R):
> summary(table_a_histogram)
a count
Min. : 7857
> Csaba, please can you copy that data into fresh tables, re-ANALYZE and
> then re-post the EXPLAINs, with stats data.
Here you go, fresh experiment attached.
Cheers,
Csaba.
db=# \d temp_table_a
Table "public.temp_table_a"
Column | Type | Modifiers
++---
a |
On Mon, 2007-09-24 at 17:14 +0200, Gábor Farkas wrote:
> will i achieve the same thing by simply dropping that table and
> re-creating it?
If you have an index/PK on that table, the fastest and most useful way
to rebuild it is to do CLUSTER on that index. That will be a lot faster
than VACUUM FUL
On Mon, 2007-09-24 at 14:27 +0100, Simon Riggs wrote:
> Csaba, please can you copy that data into fresh tables, re-ANALYZE and
> then re-post the EXPLAINs, with stats data.
Well, I can of course. I actually tried to generate some random data
with similar record count and relations between the tabl
> OK, I can confirm that. I set the statistics target for column "a" on
> table_a to 1000, analyzed, and got the plan below. The only downside is
> that analyze became quite expensive on table_a, it took 15 minutes and
> touched half of the pages... I will experiment with lower settings,
> maybe it
[snip]
Ok, I was not able to follow your explanation, it's too deep for me into
what the planner does...
> Incidentally, the way out of this is to improve the stats by setting
> stats target = 1000 on column a of ta. That will allow the optimizer to
> have a better estimate of the tail of the dis
On Fri, 2007-09-21 at 12:34 +0100, Simon Riggs wrote:
> On Fri, 2007-09-21 at 13:29 +0200, Csaba Nagy wrote:
>
> > > Can you plans with/without LIMIT and with/without cursor, for both b1
> > > and b2?
> >
> > The limit is unfortunately absolutely needed part o
On Fri, 2007-09-21 at 11:34 +0100, Heikki Linnakangas wrote:
> Which version of Postgres is this? In 8.3, a scan like that really won't
> suck it all into the shared buffer cache. For seq scans on tables larger
> than shared_buffers/4, it switches to the bulk read strategy, using only
> a few buff
On Fri, 2007-09-21 at 11:59 +0100, Simon Riggs wrote:
> Please re-run everything on clean tables without frigging the stats. We
> need to be able to trust what is happening is normal.
I did, the plan fiddling happened after getting the plans after a fresh
analyze, and I did run the plan again with
On Fri, 2007-09-21 at 10:43 +0100, Gregory Stark wrote:
> The other possibility is that Postgres just hasn't even touched a large part
> of its shared buffers.
>
But then how do you explain the example I gave, with a 5.5GB table
seq-scanned 3 times, shared buffers set to 12 GB, and top still sho
Hi all,
Postgres version: 8.2.4
Tables:
table_a(a bigint, b bigint, primary key(a, b) );
table_b1(b bigint primary key, more columns...);
table_b2(b bigint primary key references table_b1(b), more columns...);
table_b1:
~ 27M rows;
~25 more columns;
width=309 (as reported by explain selec
On Fri, 2007-09-21 at 09:03 +0100, Gregory Stark wrote:
> >> Mem: 32945280k total, 32871832k used,73448k free, 247432k buffers
> >> Swap: 1951888k total,42308k used, 1909580k free, 30294300k cached
> >
> It seems to imply Linux is paging out sysV shared memory. In fact some of
> Heikki
On Wed, 2007-07-18 at 15:36, Michael Dengler wrote:
> Row X is inserted into TableX in DB1 on server1TableX trigger
> function fires and contacts DB2 on server2 and inserts the row into
> TableY on server2.
This kind of problem is usually solved more robustly by inserting the
"change" into a
> A frequently mentioned approach to avoid the point of contention is to
> have a "totals" record and have the triggers insert "deltas" records; to
> get the sum, add them all. Periodically, take the deltas and apply them
> to the totals.
This is what we do here too. There is only one exception t
> Can someone confirm that I've identified the right fix?
I'm pretty sure that won't help you... see:
http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php
The deadlock will be there if you update/insert the child table and
update/insert the parent table in the same transaction (even
> Or are these Flash disks so slow that they compare to the HD's latency
> figures?
On sequential read speed HDs outperform flash disks... only on random
access the flash disks are better. So if your application is a DW one,
you're very likely better off using HDs.
Cheers,
Csaba.
I only know to answer your no. 2:
> 2) What about the issue with excessive locking for foreign keys when
> inside a transaction? Has that issue disappeared in 8.2? And if not,
> would it affect similarly in the case of multiple-row inserts?
The exclusive lock is gone already starting with 8.0 II
On Thu, 2007-02-22 at 22:53, Mark Stosberg wrote:
> Thanks to everyone for the feedback about vacuuming. It's been very
> useful. The pointers to the pgstattuple and Pgfouine tools were also
> helpful.
>
> I'm now considering the following plan for trying Autovacuuming again
> with 8.1. I'd like a
On Tue, 2007-02-06 at 01:35, Karen Hill wrote:
> [snip] So far I've been sitting here for about 2 million ms
> waiting for it to complete, and I'm not sure how many inserts postgres
> is doing per second.
One way is to run analyze verbose on the target table and see how many
pages it has, and then
On Thu, 2006-11-09 at 13:35, Hilary Forbes wrote:
> [snip] Is there a way that we can achieve this in Postgres? We have a
> situation whereby we have lots of web based users doing short quick
> queries and obviously the start up time for a process must add to
> their perceived response time.
Yes:
> 2d) Hints will damage the ongoing development of the optimizer by
> reducing or eliminating test cases for its improvement.
You have no evidence for this. The mindset of the postgres community you
cite further below usually mandates that you say things if you have
evidence for them... and this
> I'm not suggesting that we do that, but it seems better then embedding
> the hints in the queries themselves.
OK, what about this: if I execute the same query from a web client, I
want the not-so-optimal-but-safe plan, if I execute it asynchronously, I
let the planner choose the
best-overall-per
> Hmmm, if you already understand Visual Basic syntax, should we support
> that too? Or maybe we should support MySQL's use of '-00-00' as the
> "zero" date because people "understand" that?
You completely misunderstood me... I have no idea about oracle hints,
never used Oracle in fact. My
OK, I just have to comment...
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > These hints would outright force the planner to do things a certain way.
> > ... FROM table /* ACCESS {SEQSCAN | [[NO] BITMAP] INDEX index_name} */
>
> This proposal seems to deliberately ignore every point that has been
On Wed, 2006-10-04 at 18:02, Csaba Nagy wrote:
> > If we didn't want to add it for each list we could just add a link here:
> >
> > http://www.postgresql.org/community/lists/subscribe
OK, now that I had a second look on that page, it does contain
unsubscription info... b
> If we didn't want to add it for each list we could just add a link here:
>
> http://www.postgresql.org/community/lists/subscribe
+1
When I want to unsubscribe from a list (very rare in my case, I don't
subscribe in the first place if I'm not sure I want to get it), I start
by looking where I s
On Thu, 2006-09-28 at 09:36, Tobias Brox wrote:
> [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
On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote:
> How can I configure the vacuum to run after the daily batch insert/update?
Check out this:
http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html
By inserting the right row you can disable autovacuum to vacuum your big
tables,
> I've just fired off a "DELETE FROM table" command (i.e. unfiltered
> DELETE) on a trivially small table but with many foreign key references
> (on similar-sized tables), and I'm waiting for it to finish. It's been
> 10 minutes now, which seems very excessive for a table of 9000 rows on a
> 3
On Wed, 2006-06-21 at 18:39, jody brownell wrote:
> that is exactly what I am seeing, one process, no change, always in idle
> while the others are constantly
> changing their state.
>
> looks like someone opened a tx then is blocking on a queue lock or something.
> dang.
Don't forget to check
On Wed, 2006-06-21 at 18:21, jody brownell wrote:
> That is interesting.
>
> There is one thread keeping a transaction open it appears from ps
>
> postgres: app app xxx(42644) idle in transaction
That shouldn't be a problem on itself, "idle in transaction" happens all
the time between 2 commands
On Wed, 2006-06-21 at 17:27, jody brownell wrote:
> Our application is broken down quite well. We have two main writing processes
> writing to two separate sets of tables. No crossing over, nothign to prohibit
> the
> vacuuming in the nature which you describe.
It really doesn't matter what tab
> So, it appears my autovacuum is just NOT working... I must have screwed
> something up, but I cannot see what.
Is it possible that you have long running transactions ? If yes, VACUUM
is simply not efficient, as it won't eliminate the dead space
accumulated during the long running transaction.
[snip]
> It would be interesting to know what the bottleneck is for temp tables
> for you. They do not go via the buffer-cache, they are stored in
[snip]
Is it possible that the temp table creation is the bottleneck ? Would
that write into system catalogs ? If yes, maybe the system catalogs are
no
OK, I marked the wrong row counts, but the conclusion is the same.
Cheers,
Csaba.
> > QUERY PLAN
> > ---
> > Index Scan using ticketing_codes_uq_value_
You very likely forgot to run ANALYZE on your laptop after copying the
data. Observe the different row count estimates in the 2 plans...
HTH,
Csaba.
> QUERY PLAN
>
On Wed, 2006-03-22 at 16:35, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Oh, so in other words, SELECT * INTO temp FROM table is inherently
> > non-deterministic at the physical level, so the only way to be able to
> > allow PITR to work is to duplicate all the physical changes
> For the record, that's the wrong way round. For the data partitioning
> metadata journaling is enough, and for the WAL partition you don't need any
> FS journaling at all.
Yes, you're right: the data partition shouldn't loose file creation,
deletion, etc., which is not important for the WAL part
> Did you try mounting ext3 whith data=writeback by chance? People have
> found that makes a big difference in performance.
I'm not sure, there's other people here doing the OS stuff - I'm pretty
much ignorant about what "data=writeback" could mean :-D
They knew however that for the data partitio
Mikael,
I've just recently passed such an experience, i.e. migrating from
another vendor to postgres of a DB about the same size category you
have.
I think you got it right with the fsync turned off during migration
(just don't forget to turn it back after finishing ;-), and using tables
without
> So one very effective way of speeding this process up is giving the
> vacuum process lots of memory, because it will have to do fewer passes
> at each index. How much do you have?
OK, this is my problem... it is left at default (16 megabyte ?). This
must be a mistake in configuration, on other
Hi all,
Short story:
I have a quite big table (about 200 million records, and ~2-3 million
updates/~1 million inserts/few thousand deletes per day). I started a
vacuum on it on friday evening, and it still runs now (monday
afternoon). I used "vacuum verbose", and the output looks like:
INFO: va
Moritz,
Is it possible that you use lots of temporary tables, and you don't
vacuum the system tables ? That would cause such symptoms I guess...
Try to make a "vacuum analyze" connected as the postgres super user,
that will vacuum all your system tables too. Note that if you have a
really big bloa
On Tue, 2005-12-06 at 13:20, Joost Kraaijeveld wrote:
[snip]
> Ah, a misunderstanding: I only need to calculate an index if the user
> wants a record that is not in or adjacent to the cache (in which case I
> can do a "select values > last value in the cache". So I must always
> materialize all ro
Joost,
Why do you use an offset here ? I guess you're traversing the table
somehow, in this case it would be better to remember the last zipcode +
housenumber and put an additional condition to get the next bigger than
the last one you've got... that would go for the index on
zipcode+housenumber a
ght. Full vacuum
> fixes the problem. Thank you very much!
>
> I expect there will be less than 1000 records in the table. The index does
> obvous improvement on "SELECT task_id, username FROM download_queue WHERE
> username > '%s'" even there are only 100 r
Alex,
I suppose the table is a kind of 'queue' table, where you
insert/get/delete continuously, and the life of the records is short.
Considering that in postgres a delete will still leave you the record in
the table's file and in the indexes, just mark it as dead, your table's
actual size can gro
Hi all,
I wonder what is the main driving factor for vacuum's duration: the size
of the table, or the number of dead tuples it has to clean ?
We have a few big tables which are also heavily updated, and I couldn't
figure out a way to properly vacuum them. Vacuuming any of those took
very long amo
Christian,
Do you have foreign keys pointing to your table with ON CASCADE... ?
Cause in that case you're not only deleting your 22000 records, but the
whole tree of cascades. And if you don't have an index on one of those
foreign keys, then you might have a sequential scan of the child table
on e
First of all thanks all for the input.
I probably can't afford even the reindex till Christmas, when we have
about 2 weeks of company holiday... but I guess I'll have to do
something until Christmas.
The system should at least look like working all the time. I can have
downtime, but only for shor
table is one of which has frequently updated rows.
TIA,
Csaba.
On Thu, 2005-10-13 at 14:40, Andrew Sullivan wrote:
> On Thu, Oct 13, 2005 at 10:15:03AM +0200, Csaba Nagy wrote:
> >
> > OK, this sounds interesting, but I don't understand: why would an update
> > "c
ba.
On Thu, 2005-10-13 at 14:40, Andrew Sullivan wrote:
> On Thu, Oct 13, 2005 at 10:15:03AM +0200, Csaba Nagy wrote:
> >
> > OK, this sounds interesting, but I don't understand: why would an update
> > "chase down a lot of dead tuples" ? Should I read up on so
[snip]
> Yes, but it could be a disk issue because you're doing more work than
> you need to. If your UPDATEs are chasing down a lot of dead tuples,
> for instance, you'll peg your I/O even though you ought to have I/O
> to burn.
OK, this sounds interesting, but I don't understand: why would an u
Ok, that was the first thing I've done, checking out the explain of the
query. I don't really need the analyze part, as the plan is going for
the index, which is the right decision. The updates are simple one-row
updates of one column, qualified by the primary key condition.
This part is OK, the qu
[snip]
> Have you tried reindexing your active tables?
>
Not yet, the db is in production use and I have to plan for a down-time
for that... or is it not impacting the activity on the table ?
> Emil
>
> ---(end of broadcast)---
> TIP 9: In versions
Hi all,
After a long time of reading the general list it's time to subscribe to
this one...
We have adapted our application (originally written for oracle) to
postgres, and switched part of our business to a postgres data base.
The data base has in the main tables around 150 million rows, the wh
Barry,
I have made a similar experience, moving a big Oracle data base to
Postgres 8.03 on linux.
The first impact was similar, huge performance problems.
The main problem was bad planner choices. The cause in our case: bad
parameter types in the jdbc set methods (I guess you use Java). For
oracle
67 matches
Mail list logo
