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
(shown
as $0 in the EXPLAIN output) and the outer plan is approximately what
it would be if you'd written WHERE g.field = 'constant' instead of
WHERE g.field = (select ...)
regards, tom lane
---(end of broadcast)---
TIP 4
.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
.
The gap you are looking at is the time to do the Sort (since a sort
can't deliver the first output row until it's finished the sort).
It is gonna take a while to sort 175000 rows ... but possibly increasing
sort_mem would help.
regards, tom lane
ON special
case stops making any sense at all if it's not tied to a sort/uniq
underlying implementation.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
, because the other
way leaves you open to serious confusion about what the time value
really means.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send
should avoid the problem because array_in() caches function lookup
information for the element type's input function across multiple calls.
In 8.0 there's also a cache at the fmgr_info() level to eliminate
repeated searches for a dynamically loaded function.
regards, tom
output for each case?
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
,
because if the planner's estimates are close to reality, even this
unconstrained-cross-product join shouldn't have taken that long.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go
, (pgStatAddr.sin_addr));
to inet_aton(myip, (pgStatAddr.sin_addr)); then
it works
You were fixing the symptom and not the problem.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http
Steve Bergman [EMAIL PROTECTED] writes:
If IPv6 doesn't work, shouldn't it fall back to IPv4,
It does. That was all debugged in 7.4 --- we have not seen any cases
since 7.4 beta in which failures of this kind did not mean a
misconfigured networking setup.
regards, tom
use $1,$2,etc).
You can use PREPARE and EXPLAIN ANALYZE EXECUTE to investigate what sort
of plans result.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
might look at is ANALYZEing the tables again after
you've loaded all the new data. The row-count estimates seem way off
in these plans. You might need to increase the statistics target,
too, to get better plans.
regards, tom lane
---(end of broadcast
counts could only come from #2. I suspect
David has forgotten to run ANALYZE on the second system.
I agree that EXPLAIN VERBOSE output is not helpful...
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through
I/O costs,
so I'm a bit dubious that we should assume there is a win to be had here
just because Oracle offers the feature.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose
.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
on the assumption that
they will be executed only once. It seems entirely legitimate to me
to use the parameter values in such a case.
We might in future get braver about using sample parameter values,
but 8.0 is conservative about it.
regards, tom lane
data into a temp table and do a single
UPDATE command joining to the temp table. Or not --- quite possibly not
--- but I think it's something to think about.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched
does the query optimizer not recognize that
it can throw away those non-unique Sort/Unique passes?
Because the issue doesn't come up often enough to justify expending
cycles to check for it.
regards, tom lane
---(end of broadcast
as the maximum memory you can use,
rather than what you will use even if you don't need it all.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining
to completion.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
) then
go backwards in the index.
If you write it as
SELECT WHERE topic_id=2 ORDER BY topic_id DESC,id DESC.
then an index on (topic_id, id) will work fine. The mixed ASC/DESC
ordering is not compatible with the index.
regards, tom lane
---(end
on the
mailing lists talking about this issue, and was hard to find :( !
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/cluster.c
(note: cvsweb seems mighty slow today, but it is working...)
regards, tom lane
---(end
this option instantly ... but why in the
world are you grouping by constants anyway? You didn't say what the
datatypes of the other columns were...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists
...
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Neil Conway [EMAIL PROTECTED] writes:
Tom Lane wrote:
Because the issue doesn't come up often enough to justify expending
cycles to check for it.
How many cycles are we really talking about, though? I have a patch
which I'll send along in a few days which implements a similar
optimization
be. See
http://www.postgresql.org/docs/7.3/static/explicit-joins.html
7.4 is a bit more forgiving about this; compare
http://www.postgresql.org/docs/7.4/static/explicit-joins.html
regards, tom lane
---(end of broadcast)---
TIP 7
Vitaly Belman [EMAIL PROTECTED] writes:
What am I to do?
Reduce random_page_cost and/or increase effective_cache_size.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL
credibility to start with, unless
you can convince me you know how to turn off write buffering on the
drive...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
and returned. You might as
well just seqscan and be sure you don't read any page more than once.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Stephen Crowley [EMAIL PROTECTED] writes:
On Mon, 13 Sep 2004 21:11:07 -0400, Tom Lane [EMAIL PROTECTED] wrote:
Stephen Crowley [EMAIL PROTECTED] writes:
Does postgres cache the entire result set before it begins returning
data to the client?
The backend doesn't, but libpq does, and I think
off the checkpoint-warning alarm. The lack of
checkpoint complaints doesn't prove that this isn't a common real-world
load.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
* you'd want to use this ...
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
?
The indexes will be completely useless for that sort of query; the
reasonable choices are sort/merge or hashjoin. For either one, your
best way to speed it up is to increase sort_mem.
regards, tom lane
---(end of broadcast)---
TIP 1
they are...
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
the
conventional issues such as configuration parameters, foreign key
problems, etc. Give us some more detail about the slow INSERT
queries ...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once
sort_mem.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
think postgres normally hashes the table it thinks is smaller,
Right, it will prefer to put the physically smaller table (estimated
width*rows) on the inside.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our
:-(
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
is smarter; dunno if you want to upgrade at this point.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
is not useful. Maybe you could generalize the entry to
investigate ways of fine-tuning OS caching behavior.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
with caching
query plans. Can you get stack tracebacks from some of the stuck
processes? What do they show in ps?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
concur with the thought that using persistent connections might go a
long way towards alleviating his problem.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
these keys are not used, no need to
waste time and perform JOIN.
The bang-for-the-buck ratio on that seems much too low.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
.
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])
HyunSung Jang [EMAIL PROTECTED] writes:
can you explain to me why it's not doing that i expected??
Have you ANALYZEd this table recently? The estimated row counts seem
way off.
regards, tom lane
---(end of broadcast
looked at the inet datatype to see if that would fit your
needs?)
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL
is pretty constant, as you'd expect.
Some tests with a simple loop around a gettimeofday call yielded a value
of 2.16 microsec/gettimeofday, so there's some overhead attributable to
the EXPLAIN mechanism as well, but the kernel call is clearly the bulk
of it.
regards, tom lane
taking more than one example into account while you tweak it). But
setting seqscan off as a production setting is just a recipe for
shooting yourself in the foot.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you
and use an rtree index
with an overlaps operator. I'm too tired to work out the details,
but try searching for decorrelation in the list archives to see some
related problems.
regards, tom lane
---(end of broadcast)---
TIP 3
an
indexscan to a seqscan + sort, when it otherwise wouldn't.
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
ways of getting the planner to do something
sane with an index range bound like now() - interval.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining
...
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
each.
(Actually, I'm not sure 7.1 had EXPLAIN ANALYZE; you may have to
settle for EXPLAIN from it.)
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http
out warnings you ought to increase shared_buffers analogous to the
existing facility for noting excessive checkpointing.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
involved in the query?
You would in any case be very well advised to change the numeric
columns to integer, bigint, or smallint when appropriate. There is
a substantial performance advantage to using the simple integral
datatypes instead of the general numeric type.
regards, tom
many shared buffers you need.
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
that you'd
notice much difference, because the sync or lack of it only happens
within checkpoints.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
had lots of satisfied
switchers ...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Doug Y [EMAIL PROTECTED] writes:
Tom Lane wrote:
I have not seen any such claim, and I do not see any way offhand that
ipcs could help.
Directly from:
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
As a rule of thumb, observe shared memory usage of PostgreSQL
across that boundary ;-)), there's no way we are going to buy into
this sort of project in hopes of a 3% win.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
, *without* Gavin or Neil's Futex patch.
Hmm, in that case the cost deserves some further investigation. Can we
find out just what that routine does and where it's being called from?
regards, tom lane
---(end of broadcast)---
TIP
suppose those are the ones associated with suppressing SIGPIPE
during send(). It looks to me like those should go away in 8.0 if you
have compiled with ENABLE_THREAD_SAFETY ... exactly how is PG being
built in the current round of tests?
regards, tom lane
Mark Wong [EMAIL PROTECTED] writes:
On Fri, Oct 15, 2004 at 05:27:29PM -0400, Tom Lane wrote:
Hmm, in that case the cost deserves some further investigation. Can we
find out just what that routine does and where it's being called from?
There's a call-graph feature with oprofile as of version
safe for large shared_buffers
values. (See also
http://archives.postgresql.org/pgsql-performance/2004-10/msg00218.php)
Any objection to doing this for 8.0?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe
for a boolean column will save *zero* effort unless all the
columns in the table are booleans.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
.
Simple queries generally don't take that long to plan. Complicated
queries do, but I think the reusability odds go down with increasing
query complexity.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched
lock out writers. Or didn't you notice the self-
contradictions in what you just said?
Our current scalability problems dictate reducing such contention, not
adding whole new sources of it.
regards, tom lane
---(end of broadcast
that that
estimate was way low and so the nestloop is taking forever. You might
try SET enable_nestloop = off as a crude way of avoiding that trap.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once
in the column?
Have you done an ANALYZE recently on the table, and if so can you show
us the pg_stats row for the column?
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
the date
arithmetic on the client side and send over a simple literal constant.
If that's not practical you can fake it with a mislabeled IMMUTABLE
function --- see the list archives for previous discussions of the
same issue.
regards, tom lane
---(end
tip either, although there was some talk
of doing something in time for 8.0.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
' IS NULL) OR (case_filed_date '2004-09-20'))
I was hoping that the null comparisons would get folded out by the planner
relatively cheaply.
You could teach eval_const_expressions about simplifying NullTest nodes
if you think it's important enough.
regards, tom lane
on index on datetime column)
The question isn't whether it can use it as an indexscan bound; the
question is whether it can derive an accurate rowcount estimate.
The issue is exactly that STABLE functions work for one but not the
other.
regards, tom lane
to tell without a lot more
detail about what case you were testing.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
condition. It can't do very much with the
equivalent condition in the original form, though, and in fact ends up
drastically underestimating the number of matching rows (86 vs reality
of 3021). That leads directly to a bad plan choice :-(
regards, tom lane
second query is finding all the disk pages it needs in
kernel disk cache, because they were all read in by the first query.
This has little to do with cursor versus non cursor, and everything
to do with hitting recently-read data again.
regards, tom lane
we only consider that for GROUP BY.
The DISTINCT planning code is fairly old and crufty and hasn't been
redesigned lately.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose
more-profitable ways
to invest our coding effort and planning cycles.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
401 - 500 of 3915 matches
Mail list logo
