pends on having a framework to do
replanning at all. I intend to take a look at that once Neil has
created such a framework ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
rable to the declared type of the corresponding referenced
column.
It doesn't say that it has to be indexable, and most definitely not that
there has to be an index.
regards, tom lane
---(end of broadcast)---
TI
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
>>> Other than spec compliance, you mean? SQL99 says
>>>
>>> ... The declared type of each referencing column shall be
>>> comparable to the declared ty
e
useful to compare what VERBOSE has to say to the changes in
reltuples/relpages.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
d
go back to recording just the actual stats.
Sound reasonable? Or was I right the first time and suffering brain
fade today?
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
e a usable
index on the FK column (since indexes on the FK table may not have been
built yet when the constraint is declared), and shouldn't anyway because
there are reasonable usage patterns where you don't need one.
regards, tom lane
---(
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Fri, 2005-03-25 at 15:22 -0500, Tom Lane wrote:
>> 2. Dead tuples don't have that much influence on scan costs either, at
>> least not once they are marked as known-dead. Certainly they shouldn't
>> be charged at f
e better to issue the vacuum synchronously
as part of the batch updating script, I feel.
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])
Bruce Momjian writes:
> Tom Lane wrote:
>> I'm not sure if autovacuum could be taught to do that --- it could
>> perhaps launch a vacuum as soon as it notices a large fraction of the
>> table got deleted, but do we really want to authorize it to launch
>> VACUUM
own in 7.4.2.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
ht allow us to support FOR UPDATE in
subqueries, as well, but I haven't looked at the details. (Whether that
is a good idea is another question --- the problem of pulling rows that
aren't nominally necessary, and thereby locking them, would apply in
spades.)
s ... but it's not that part.)
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
ggers ;-). Do they
always know at the time of preparing a plan which way it will be used?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
hat at the LIMIT stage.
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
rder "((sl join cl) join ts)" unless
you have an outer join in the mix. I think that's generally a good
heuristic, and am disinclined to remove it ...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
yone see any problem with
> turning off the enable_nestloop option right before executing my query and
> turning it back on afterwards?
That's what it's there for ... but it would be useful to look into why
the planner gets it so wrong without that hint. Could we see EXPLAIN
AN
If you have both equalities and inequalities in an index condition, you
always want the equalities to be on the higher-order keys. Otherwise
the scan will involve wasted scanning over index entries that match
only some of the conditions. (Think about the ordering of a multicolumn
index to see why th
rows in every table? Which PG version exactly?
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
inserting process), so with say a 7200RPM
drive (120 revs/sec) the above is a pretty good fraction of the
theoretical limit.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
so, tweaking the bgwriter parameters might
improve matters.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
which you
can get in the higher-end hardware RAID controllers. Otherwise you're
going to have problems whenever the power goes away unexpectedly.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ion to make the query simpler.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
7;s failing
to cache the information that's a pretty serious performance hit.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
interesting. I'm wondering for example
about the incidence of duplicate index keys.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Christopher Petrilli <[EMAIL PROTECTED]> writes:
> On Apr 4, 2005 11:52 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Could we see the *exact* SQL definitions of the table and indexes?
> Of course, this is a bit cleansed, since it's an internal project, but
> o
Christopher Petrilli <[EMAIL PROTECTED]> writes:
> On Apr 4, 2005 12:23 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> do a test run with *no* indexes on the table, just to see if it behaves
>> any differently? Basically I was wondering if index overhead might be
>
Christopher Petrilli <[EMAIL PROTECTED]> writes:
> On Apr 4, 2005 10:36 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> The indicated fix of course is to increase shared_buffers.
> Any idea where it should be set?
Not really. An upper bound would be the total size of the fini
ounterproductive for this situation?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
to join to about one postcodes row,
it's possible that what the planner did for you was actually the
optimal thing anyhow. I'm not sure that any range-capable index would
be faster than just scanning through 160 entries in memory ...
regards, tom lane
--
list and I'll run a profile.
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
Arjen van der Meijden <[EMAIL PROTECTED]> writes:
> On 6-4-2005 20:09, Tom Lane wrote:
>> Comparing the nestloop case to the hash case does make one think that
>> there's an awful lot of overhead somewhere, though. Two int2
>> comparisons ought not take very long
ents of the
stream. (By the same token, it's unlikely that deliberately aggregating
such calls would be much of a win.)
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Arjen van der Meijden <[EMAIL PROTECTED]> writes:
> On 6-4-2005 19:42, Tom Lane wrote:
>> Wrong index ... what you probably could use here is an index on
>> data_main.range, so that the query could run with postcodes as the
>> outer side. I get such a plan by default wi
an be treated as a range restriction on a.x for this purpose,
but I'm much less sure that the same is true of
a.x > b.y AND a.x < c.z
Thoughts?
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])
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote:
>> Can anyone suggest a more general rule? Do we need for example to
>> consider whether the relation membership is the same in two clauses
>> that might be
ipate that bitmap-driven index scans will change things
considerably here. The range of usefulness of pure seqscans will
drop drastically...
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our
d at this :-(. Consider
building a special backend binary with the functions of interest
statically linked into it...
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
it is far from widespread.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> Can anyone suggest a more general rule?
> I think it makes sense to guess that a smaller fraction of the rows will
> be returned when a column value is bounded above and below than
o get an approximation...
7.1 psql hasn't got \timing either ...
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
UX_PROFILE if not on Linux, of course.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I'm a bit surprised it didn't choose a seqscan and sort
instead. Or even more likely, forget the merge joins altogether and use
hash joins --- the other tables are plenty small enough to fit in hash
tables.
regards, tom lane
---
orkaround for this in our CVS, but it's not in
8.0.*.) I think we can still conclude that the indexscan on
tblassociate is most of the cost, but I wouldn't venture to say that
it's exactly such-and-such percent.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
"Joel Fradkin" <[EMAIL PROTECTED]> writes:
> random_page_cost = 1.2#4 # units are one sequential page
> fetch cost
That is almost certainly overoptimistic; it's causing the planner to
use indexscans when it shouldn't. Try 2 or 3 or thereabouts.
Mischa <[EMAIL PROTECTED]> writes:
> Quoting Tom Lane <[EMAIL PROTECTED]>:
>> WHERE a.x > b.y AND a.x < 42
> Out of curiosity, will the planner induce "b.y < 42" out of this?
No. There's some smarts about transitive equality, but none about
transi
27;s see you produce
one for dblink() for instance ...
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Sat, Apr 09, 2005 at 12:00:56AM -0400, Tom Lane wrote:
>> But with all due respect to Joe, I think the reason that stuff got
>> trimmed is that it didn't work very well. In most cases it's
>> *hard*
al.
I think it's highly unlikely that the DELETE statement did it.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
y probably need indexes on the far end. Also check for
datatype discrepancies.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
ut if you're desperate
you could load up a play server with your data and test.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister Yo
ign keys referencing this table from other tables?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
gist (category)
> "records_cid_idx" btree (cid)
> "records_uid_idx" btree (uid)
Hmm ... my suspicion would fall first on the GIST index, to tell you the
truth. Did you try dropping that one?
regards, tom lane
---(
n keys could be the problem,
particularly if the referencing columns don't have indexes.
Also, maybe the table is just bloated? What does VACUUM VERBOSE say
about it?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
hat kinda sounds like "flaky network" to me, but I could be wrong.
In any case, you'd have better luck asking kernel or network hackers
about this than database weenies ;-)
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:
>> This is a network-level issue: the TCP stack on your machine knows the
>> connection has been closed, but it hasn't seen an acknowledgement of
>> that fact from the othe
te?
It doesn't know it's appropriate. There's nothing constraining the FK
to be positive, after all.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
s sort would be faster than scanning an index that exactly matched
the sort order the Merge Join needed ... and it was wrong :-(
So this is just the usual sort of question of "are your stats up to
date, maybe you need to increase stats targets, or else play with
random_page_co
rs ago, and you can see that the case for doing I/O scheduling
in the kernel and not in the drive is pretty weak.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if you
ld discard
the whole queue and replace it with one entry that says "run the
wholesale check again when we are ready to fire triggers". I'm not
sure how to detect this efficiently, though --- the trigger manager
doesn't presently know anything about FKs being different from
any
re-add the FK constraint.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
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
ons, but I suppose it's the same deal: cost of undoing
a transaction in Oracle is proportional to the number of rows it
changed. There's also the little problem that the space available for
UNDO logs is limited :-(
As against which, they don't have to VACUUM. So it's a tradeoff.
better than A's ... which
is unlikely but not impossible, considering the cylinders are probably
closer together).
Usually there's some-of-each involved, so it's hard to make any
definite statement without more facts.
regards, tom lane
-
Kevin Brown <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> The reason this is so much more of a win than it was when ATA was
>> designed is that in modern drives the kernel has very little clue about
>> the physical geometry of the disk. Variable-size tracks, bad-
that doesn't have any low-level timing
information. If there are multiple random requests on the same track,
the drive has an opportunity to do better than that --- if it's got all
the requests in hand.
regards, tom lane
parse/plan overhead is in there too.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
te_clientnum on
> tblassociate a (cost=0.00..10786.17 rows=177352 width=53) (actual
> time=0.166..1126.052 rows=177041 loops=1)"
> " Index Cond: ((clientnum)::text = 'SAKS'::text)"
> "Total runtime: 12287.502 ms"
It strikes me as odd that the thing isn't considering hash joins for
at least some of these steps. Can you force it to (by setting
enable_mergejoin off)? If not, what are the datatypes of the join
columns exactly?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
are selecting a lot of items rows
then it won't be the best plan.
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
huge amount of it for 8.0.
However, these numbers don't prove much either way.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
s join for some reason ... and I
think I see why. It's not accounting for the combined effect of the
two hash clauses, only for the "better" one. What are the statistics
for tbljobtitle.id and tbljobtitle.clientnum --- how many distinct
values of each, and are the distributions
other
settings ... with no information about exactly *what* is slow, it's
hard to say.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-noma
re than one row (else the join
might produce more result rows than the original query).
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
ts to run
> better again, so i think the problem is not related to a specific query.
It sounds like you may not have the FSM settings set large enough for
your database. The default settings are only enough for a small DB
(perhaps a few hundred meg).
regards, tom lane
-
oat over time it probably means you need to tweak your autovacuum
settings. I'm not much of an autovacuum expert, but maybe someone
else can help you there.
You might want to keep track of physical file sizes over a period of
time and try to determine exactly where the bloat is happe
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> It would be interesting sometime to try to teach the planner about
>> inlining SQL-language functions to become joins. That is, given
> The Inlining of the function is presumabl
s
not any less intrusive in terms of locking, but it's often faster and it
avoids the index bloat problem (since it effectively does a REINDEX).
regards, tom lane
---(end of broadcast)---
TIP 5: Have yo
d max_fsm_pages?
Within that one database, yes --- don't forget you must sum these
numbers across all DBs in the cluster. Also you need some slop
in the max_fsm_pages setting because of quantization in the space
usage. It's probably easier to let VACUUM VERBOSE do the calculation
for yo
r criterion.
I think you can usually get away with setting max_fsm_pages to less than
your actual disk footprint, but I'm not sure how much less. It'd
probably depend a lot on your usage pattern --- for instance,
insert-only history tables don't need any FSM space.
y need enough FSM to record the
free space you'll need until the next vacuum.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
s used for storing TIDs of to-be-moved
> tuples for index cleanup ... how does it relate to the above?
TIDs of to-be-deleted tuples, actually. Movable tuples aren't stored,
they're just found on-the-fly during the back-to-front pass.
regards, tom lane
---
row not just part of it ...) There's probably no point in
showing the target=1000 version, but maybe target=100 would be
informative.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
that the issue is not considered is just wrong.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
postgresql proces is
> using 4-5% CPU.
It's very hard to believe that *neither* disk nor CPU is maxed.
Can we see a reproducible test case, please?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
NALYZE, please?
(In general, any time you are complaining about planner misbehavior,
it is utterly pointless to give only planner estimates and not reality.
By definition, you don't think the estimates are right.)
regards, tom lane
---(end of broad
an every six hours", but I'm trying not to jump to conclusions.
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
heck the runtimes for the same query with
LIMIT 3000, ie, see if a tenth as much data takes a tenth as much
processing time or not. The backend code should be pretty darn
linear in this regard, but maybe pgadmin isn't.
regards, tom lane
---(e
operators in the join condition)
so you'd have to raise one or the other of these parameters
to model this situation accurately. But I have a hard time
believing that cpu_tuple_cost is really as high as 0.1.
It seems more likely that the cpu_operator_cost is underestimated,
which lead
Richard van den Berg <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Perhaps you are incurring a datatype conversion cost?
> Not that I can tell.
No, apparently not. Hmm ... timestamp_cmp_internal is just a couple of
isnan() checks and one or two floating-point compares.
ion? Facts, please, not inferences.
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
David Roussel <[EMAIL PROTECTED]> writes:
> |dave_data_update_eventsr 1593600.0 40209
> |dave_data_update_events_event_id_key i 1912320.0 29271
Hmm ... what PG version is this, and what does VACUUM VERBOSE on
that table show?
nge the plan.
Feel free to propose better cost equations.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
n't a chance coincidence of names that made you think it did?
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
>> Feel free to propose better cost equations.
> Where would I look in code to see what's used now?
All the gold is hidden in src/backend/optimizer/path/costsize.c.
regards, tom lane
--
le GB, but running oid2name again returns no result on
> the oid or filenode.
What is the filename exactly (full path)?
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
ommitted yet. Do you have any apps that create
and fill a table in a single transaction?
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
e of the function is turning into an infinite loop --- could
it be finding some sort of cycle in your page data? You might want to
add some RAISE NOTICE commands to the loop so you can track what it's
doing.
regards, tom lane
---(end of broadcast)-
t; = "ClimateChangeModel40"."ClimateId")
Yeah, that's what jumped out at me too. It's not the full explanation
for the join number being so far off, but this one at least you have a
chance to fix by updating the stats on ClimateChangeModel40.
re
have nothing against adopting a different formula, if you can find
something with a comparable amount of math behind it ... but I fear
it'd only shift the failure cases around.
regards, tom lane
---(end of broadcast)---
TIP 9
table.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
1 - 100 of 4389 matches
Mail list logo
