it will
have only negligible impact on the particular problems we're discussing
in this thread.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
sort_mem that big
normally, because there may be many sorts happening in parallel,
but in a data-loading context there'll just be one active sort.)
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your
happening in parallel,
but in a data-loading context there'll just be one active sort.)
Doesn't this provide a reason for CREATE INDEX not to honour sort_mem?
Already done for 7.5.
http://archives.postgresql.org/pgsql-committers/2004-02/msg00025.php
regards, tom lane
ms
(8 rows)
What I would like to know is if there are better ways to do the join?
What have you got sort_mem set to? You might try increasing it to a gig
or so, since you seem to have plenty of RAM in that box ...
regards, tom lane
---(end
tables recently?
If the stats are up to date but still not doing the right thing,
you might try increasing the statistics target for the larger
table's tag_id column. See ALTER TABLE SET STATISTICS.
regards, tom lane
---(end of broadcast
or
their replacement requires them to be finally written. So if there is no
spare IO bandwidth, it makes things worse.
Right, the trickle writes could be wasted effort.
regards, tom lane
---(end of broadcast)---
TIP 6: Have
...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Jaime Casanova [EMAIL PROTECTED] writes:
i have a question, is there any advantages in using numeric(1) or numeric(2)
in place of smallint?
Performance-wise, smallint is an order of magnitude better.
regards, tom lane
---(end of broadcast
...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
are buying into with such a thing is multiple copies
of critical state. It may be only one bit rather than several words,
but updating it is no less painful than if it were a full copy of the
tuple's commit status.
regards, tom lane
---(end of broadcast
...
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
extracted from the item table. Have you ANALYZEd that table lately?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
operations by requiring more I/O. So it's far from clear
that this would be a win, even for those who care only about select
speed.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL
. Perhaps a
suitably-enlarged version of that section could cover this.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL
using?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
doesn't support concurrent operations?
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
held by
processor A.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Robert Creager [EMAIL PROTECTED] writes:
I just figured out what was causing the problem on my system Monday.
I'm using the pg_autovacuum daemon, and it was not vacuuming my db.
Do you have the post-7.4.2 datatype fixes for pg_autovacuum?
regards, tom lane
Robert Creager [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] confessed:
Do you have the post-7.4.2 datatype fixes for pg_autovacuum?
No. I'm still running 7.4.1 w/associated contrib. I guess an upgrade is in
order then. I'm currently downloading 7.4.2 to see what the change
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
... The SMP issue seems to be not with whether there is
instantaneous contention for the locked datastructure, but with the cost
of making it possible for processor B to acquire a lock recently held by
processor A.
I see. I don't
tests to start postmaster with -i so the tests
: can be run on systems that don't support unix-domain sockets
Done long ago.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once
: I believe that I did check the self-contained
test case we eventually developed against CVS tip on one of Red Hat's
SMP machines, and indeed it was unhappy.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all
behave better though.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
there.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
, clog is not a very high-I/O-volume thing, so in
one sense it doesn't much matter which drive you put it on. But it
seems to me that clog acts much more like ordinary table files than it
acts like xlog.
regards, tom lane
---(end of broadcast
or two. Perhaps with the
recent schedule change there will be some time for performance tuning
before we go beta.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
(database size scale factor) to
exceed -c (number of concurrent clients) for meaningful results.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
versions at many tasks (at the expense of safety) until the new sync()
code is put in.
... which was three days ago. Why are we still speculating?
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive
: (((direction = 'D'::text) OR (direction = 'B'::text)) AND
(deletiondate IS NULL))
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
).
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
on the
most-heavily-used index of each table.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
issues
to deal with.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
then drive the EXISTS
test efficiently.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message
? Seems like your
results suggest that you need to lower it.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
to be
lots of noise in any particular measurement.
But in general, setting random_page_cost to 1 is only reasonable when
you are dealing with a fully-cached-in-RAM database, which yours isn't.
regards, tom lane
---(end of broadcast
.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
.
It seems somewhat interesting that you see the problem only sometimes
and not every time, but there's not much point in investigating further
if it turns out the problem is already fixed.
regards, tom lane
---(end of broadcast
the pl/pgsql has finished executing.
This is quite hard to believe, unless your pl/pgsql is doing something
as unfriendly as LOCKing the table.
Do you want to post a more complete description of your problem?
regards, tom lane
---(end
. This
definitely suggests that you've been lax about vacuuming this table.
I'm wondering whether pmdoc might not be overdue for vacuuming as
well.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe
.
regards, tom lane
---(end of broadcast)---
TIP 3: 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
prove much.
If you think it's actually stuck waiting for something, try attaching to
the REINDEX backend process with gdb to get a stack trace. That would
at least give some idea what it's waiting for.
regards, tom lane
---(end of broadcast
Frank van Vugt [EMAIL PROTECTED] writes:
What could I do to make it easier to choose a better plan?
Increase sort_mem. You want it to pick a hashed subplan, but
it's not doing so because 64000 rows won't fit in the default
sort_mem.
regards, tom lane
.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
for you to install a backend built with
--enable-debug and get a more reliable backtrace?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Nick Trainor [EMAIL PROTECTED] writes:
What am I missing here?
The ORDER BY query has to evaluate the function at *every* row of the
table before it can sort. The other query was only evaluating the
function at twenty rows.
regards, tom lane
issue is or not.)
Prior versions hold this lock during flush as well, but it's less likely
that the same page an active process is interested in is being written
out, since before the bgwriter only the least-recently-used page would
be a candidate for writing.
regards, tom
. But there are nearby places that might have
FS-dependent behavior. Can you do anything about my request for
a stack trace from a debug-enabled build?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe
Merlin Moncure [EMAIL PROTECTED] writes:
Tom Lane wrote:
Does it help if you change FlushBuffer to release buffer lock while
flushing xlog?
Putting your change in resulted in about a 15% increase in insert
performance. There may be some quirky things going on here with NTFS...
I did
the sort order.
Can you provide a self-contained example?
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so
at all because the output
of the indexscan is already known to be sorted by s.series_id. I was
thinking of a sort with more or fewer sort columns, but that's not the
issue here.
regards, tom lane
---(end of broadcast)---
TIP
50)
Obviously this is on toy tables, but the point is that the constraint
does get pushed down through the GROUP BY when appropriate.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Stephen Frost [EMAIL PROTECTED] writes:
* Tom Lane ([EMAIL PROTECTED]) wrote:
[... thinks for awhile ...] It seems possible that they may use sort
code that knows it is performing a DISTINCT operation and discards
duplicates on sight. Given that there are only 534 distinct values,
the sort
there are not many distinct values of version.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
?
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Device).
There are a lot of horror stories concerning running databases (not only
Postgres) over NFS. I wouldn't recommend it. Dunno anything about NBD
though.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9
it as root definitely doesn't know what they're doing).
It is a wizard's tool. Get knowledgeable advice from the PG lists
before you use it rather than after.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9
(8K at a time) to
/var/lib/pgsql/data/pg_clog/000E until it's longer than 73728 bytes.
I'd use something like
dd bs=8k count=1 /dev/zero /var/lib/pgsql/data/pg_clog/000E
assuming that your system has /dev/zero.
regards, tom lane
---(end
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Even easier:
SELECT * FROM pg_stat_activity;
But note you must enable stats_command_string to make this very useful.
regards, tom lane
---(end of broadcast)---
TIP 9
Jim [EMAIL PROTECTED] writes:
I have one performance issue... and realy have no idea what's going on...
[yawn...] Cast the constants to bigint. See previous discussions.
regards, tom lane
---(end of broadcast)---
TIP 9
involved here that we've not recognized yet.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Klint Gore [EMAIL PROTECTED] writes:
On Sun, 27 Jun 2004 23:29:46 -0400, Tom Lane [EMAIL PROTECTED] wrote:
[yawn...] Cast the constants to bigint. See previous discussions.
Would there be any way of adding some sort of indicator to the plan as
to why sequential was chosen?
Not really
, tom lane
---(end of broadcast)---
TIP 3: 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
are done in the same process.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
effect, because less I/O will
be triggered when not much has changed since the last one. So
you'd want to wait a bit between experiments.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once
...
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
stack with bt, and look in pg_locks to see
what lockmanager locks it is holding or waiting for. If you do not find
one, then the deadlock theory is disproved, and we're back to square
one.
regards, tom lane
---(end of broadcast
is needed...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
loops=1)
Index Cond: ((species)::text = 'Homo sapiens'::text)
Have you ANALYZEd this table recently? If so, maybe you need a larger
statistics target for the species column. The estimated row count
shouldn't be off by a factor of seventy...
regards, tom lane
is not ...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
it completely.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
doubling
sort_mem temporarily to see how it does? Or even raising shared_buffers?
Raising shared_buffers seems unlikely to help. I do agree with raising
sort_mem --- not so much to make the merge faster as to encourage the
thing to try a hash join instead.
regards, tom lane
representative.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
statement --- it's only safe in interactive queries,
where you don't care that the value is reduced to a constant during
planning instead of during execution.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore
with array
GiST code in a multicolumn index. I still don't really know why it
failed, but after two days building the index I gave up.
Sounds like a bug to me. Could you put together a test case?
regards, tom lane
---(end of broadcast
or prepared statement. What you're doing here is to push the
freezing of the now value even further upstream, namely to initial
parsing of the command.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your
.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
weren't specific
about the data model ...
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Merlin Moncure [EMAIL PROTECTED] writes:
Plus, your where clause does not guarantee results.
No, but in combination with the ORDER BY it does. Please note also
that the offset would *always* be one, so your gripe about it not
scaling seems misguided to me.
regards, tom
Tom Lane [EMAIL PROTECTED] writes:
Merlin Moncure [EMAIL PROTECTED] writes:
Plus, your where clause does not guarantee results.
No, but in combination with the ORDER BY it does.
Oh, wait, you're right --- I'm mis-visualizing the situation.
Hmm, it sure seems like there ought to be an easy
over so many years, ripping
it out meanwhile doesn't seem appropriate.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
The only reason the code in parse_expr.c appears new is that the
functionality used to be in gram.y.
Ah, that was what I was missing. Though it's odd since it seems there was code
in parse_expr.c to handle the = case
about the datatypes involved ...
If it is, the other possibility is that you need to increase sort_mem
to accommodate the hash table.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire
.)
It strikes me that what you really want for ISAM is to improve the
cursor mechanism so it will do the things you need. I'm not sure
what's involved, but let's talk about that angle for a bit.
regards, tom lane
---(end of broadcast
Stan Bielski [EMAIL PROTECTED] writes:
On Thu, 29 Jul 2004, Tom Lane wrote:
Are you sure the join condition is hashjoinable? You didn't say
anything about the datatypes involved ...
My apologies. The columns that I want to join are both type 'inet'.
Shouldn't that be hashjoinable?
Depends
. There are no other solutions.
(Well, if you were really desperate you could create a set of
mergejoinable text op bigint comparison operators, and then 7.2
would be able to cope; but I should think that updating to 7.4 would
be much less work.)
regards, tom lane
-element table.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
spent on general-purpose disk buffers or kernel
cache.
Note though that this is just informed opinion, as I've never done or
seen any benchmarks that examine the results of changing wal_buffers
while holding other things constant. Has anyone tried it?
regards, tom lane
for a way to turn off syncing completely for a table.
There isn't one, and I'm not eager to invent one.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Litao Wu [EMAIL PROTECTED] writes:
Here is info from pg_lock:
All those locks are already granted, so they are not much help in
understanding what PID 18951 is waiting for. What row does it have
with granted = 'f' ?
regards, tom lane
---(end
Litao Wu [EMAIL PROTECTED] writes:
Did I miss something?
Your join omits all transaction locks.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
?
... the database is regularly vaccuumed.
Not regularly enough, perhaps ... or else you need to increase the free
space map size parameters. In any case you'll probably need to do one
round of vacuum full to get this table back within bounds.
regards, tom lane
the transaction lock rows
(because they have NULLs in the relation field).
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
.
If you just want to see whether anything is happening, do a VACUUM
VERBOSE on that table and note the amount of storage in the toast table
as compared to the main table.
regards, tom lane
---(end of broadcast)---
TIP 2: you can
are really
getting (see EXPLAIN) rather than just assuming that some indexes chosen
at random will do what you need.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
that each index costs time to maintain during
inserts/updates. So adding an index just because it makes a few queries
a little faster probably isn't a win. You need to make tradeoffs.
regards, tom lane
---(end of broadcast)---
TIP 6
a float8 or bigint
under the hood, and is no more expensive to compare than those datatypes.
Timestamps *are* expensive to convert for I/O, but comparison does not
have to do that.) I wouldn't recommend kluging up your data schema just
for that.
regards, tom lane
adding such, but for 8.0 only the more common cases such as
int-vs-bigint are covered.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining
and the server needs to be restarted.
It sounds to me like you have got hardware problems. Get out your
memory and disk tests ...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once
301 - 400 of 3915 matches
Mail list logo