ved_by_id);
What that looks like is it took the system an unusually long time to
notice that it needed to cancel the autovacuum to avoid a deadlock
with the CREATE INDEX. Was either process consuming a noticeable
amount of CPU during that interval? Do you have deadlock_timeout
set higher than the
but I really doubt
you want the side-effects of that.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
he
live key-space constantly migrates to the right, so that the original
upper-level key splits would become impossibly unbalanced. This isn't
all that unusual a situation; consider timestamp keys for instance,
in a table where old data gets flushed regularly.
regards, tom lane
--
Sent
unstructured data, but they are
less great when you want to do relational-ish things on subfields.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
E STATISTICS. But it
didn't get done for v10.
If we do look at that as a substitute for "make an expression index just
so you get some stats", it would be good to have a way to specify that you
only want the standard ANALYZE stats on that value and not the extended
ones.
.653
>> rows=508 loops=1)
I think the reason it's discarding the preferable plan is that, with this
huge increment in the estimated cost getting added to both alternatives,
the two nestloop plans have fuzzily the same total cost, and it's picking
the one you don't want on the basis of some s
Jim Nasby <j...@nasby.net> writes:
> On 10/8/17 2:34 PM, Tom Lane wrote:
>> Why has this indexscan's cost estimate changed so much?
> Great question... the only thing that sticks out is the coalesce(). Let
> me see if an analyze with a higher stats target changes anyth
I was just about to ask you about that. If this is under a Gather node,
I believe that the numbers include time expended in all processes.
So if you had three or more workers these results would make sense.
regards, tom lane
--
Sent via pgsql-performance mailing list
Neto pr <neto...@gmail.com> writes:
> I need to know the height of a B-tree index (level of the leaf node
> farthest from the root).
pageinspect's bt_metap() will give you that --- it's the "level"
field, I believe.
regards, tom lane
--
Sent
emains on disk till you do end the transaction.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
'm not sure.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
are strings and you're using C locale on the faster machine but
some non-C locale on the slower. strcoll() is pretty darn expensive
compared to strcmp() :-(
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To ma
Whether that's got anything directly to do with your original problem is
hard to say. Joins to subqueries, which we normally lack any stats for,
tend to produce pretty bogus selectivity numbers in themselves; so the
original problem might've been more of that nature.
risk of taking a serious performance hit due to a bad plan.
An alternative you might consider, if simplifying the input queries
is useful, is to put the fixed conditions into a view and query the
view instead. That way there's not an enforced evaluation order.
regards, tom
Justin Pryzby <pry...@telsasoft.com> writes:
> On Fri, Nov 10, 2017 at 04:19:41PM -0500, Tom Lane wrote:
>> One idea is to say that relpages = reltuples = 0 is only the state that
>> prevails for a freshly-created table, and that VACUUM or ANALYZE should
>> always set
o pg_class. It's not like that's going
to be a noticeable percentage increase in the row width ...
> But is there a better way (I don't consider adding a row of junk to be a
> significant improvement).
Not ATM.
regards, tom lane
--
Sent via pgsql-performance mailing li
incorporate some
perhaps-configurable amount of risk aversion in its choices.
regards, tom lane
PS: please do not top-post, and do not quote the entire darn thread
in each message.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to
ow_Query_Questions
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
d holding it for a second or two. If so,
turning on log_lock_waits might provide some useful info.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
bright either.
Changing this in back branches might be too much of a behavioral change,
but it seems like we oughta change HEAD to apply standard selectivity
estimation to the HAVING clause.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgr
?version=7.3idoc=0file=explicit-joins.html
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
will be affected by caching.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Manfred Koizar [EMAIL PROTECTED] writes:
On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane [EMAIL PROTECTED]
wrote:
Try reducing random_page_cost
With index scan cost being more than 25 * seq scan cost, I guess that
- all other things held equal - even random_page_cost = 1 wouldn't
help.
Oh
,
if only so I can try to fix it in future releases, but without useful
information I'll just have to write this off as an unsubstantiated report.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Martin Foster [EMAIL PROTECTED] writes:
As a side note, would you recommend disabling
fsync for added performance?
Only if you are willing to sacrifice crash-safety in the name of speed.
regards, tom lane
---(end of broadcast
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
However, it looks to me like the subquery-scan-outside plan probably
is the faster one, on both my machine and yours. I get
Woah, that's pretty whacky. It seems like it ought to be way faster to do a
single sequential
.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
of?
Only possible if you don't have the FSM parameters set high enough.
Infrequent vacuuming means you need more FSM space, btw.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives
on HPUX (with a SCSI disk) and Linux (with an IDE disk, and a different
filesystem). I didn't see too much difference between 'em. RAID might
alter the equation, or not.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe
analyze', the whole thing slows WAY down to where each run can take
10-15 minutes.
Could we see EXPLAIN ANALYZE for the deletion query in both the fast and
slow states?
regards, tom lane
---(end of broadcast)---
TIP 8: explain
.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
=?ISO-8859-1?Q?SZUCS_G=E1bor?= [EMAIL PROTECTED] writes:
do regex ops (~, ~*) use index scan in non-C locales? Is it worth to
convert LIKE to regex?
The locale issues are the same either way.
regards, tom lane
---(end of broadcast
the casting quite right? (For instance,
timestamp isn't timestamp with time zone ...)
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
contents. PG's WAL logic can recover lost file
contents, but we have no way to help out the filesystem if it's lost
metadata.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
that the table
knows about new indexes.
You do not need to ANALYZE to get the system to notice new indexes.
4. i wish i had a rename index command to rename _swap
to its original index name.
You can rename indexes as if they were tables.
regards, tom lane
the profile depends on statistical
sampling. I like to have about a minute of accumulated runtime before
trusting the results. Repeat the same query multiple times if needed.
regards, tom lane
---(end of broadcast)---
TIP 2: you can
. I'd
really like to see some profiling of the poor-performing
external-storage case, so we can figure out what's going on.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send
the trouble.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
to find out what works well and what features are needed.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Joe Conway [EMAIL PROTECTED] writes:
Tom Lane wrote:
Ah-hah, I've sussed it ... you didn't actually change the storage
representation. You wrote:
Yeah, I came to the same conclusion this morning (update longdna set dna
= dna || '';), but it still seems that the chunked table is very
Sebastien Lemieux [EMAIL PROTECTED] writes:
On Wed, 6 Aug 2003, Tom Lane wrote:
The idea here is to make sure that the planner's statistics reflect the
full state of the table, not the empty state. Otherwise it may pick
plans for the foreign key checks that are optimized for small tables.
I
scott.marlowe [EMAIL PROTECTED] writes:
On Wed, 6 Aug 2003, Tom Lane wrote:
One obvious question is whether you have your foreign keys set up
efficiently in the first place. As a rule, the referenced and
referencing columns should have identical datatypes and both should
be indexed. (PG
...)
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
.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
.
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
.
Journalling file contents updates, as opposed to filesystem metadata,
should be redundant with what we do in WAL. So I'd recommend
journalling metadata only, if that option is available (and if Postgres
stuff is the only stuff on the disk...)
regards, tom lane
: 37383.02 ms
scott=#
So it looks like the external-storage optimization for substring() does
work as expected, once you get the data into the right format ...
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget
an update
to 7.3.4 would be a more profitable use of time.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
;
explain analyze ... query ...
If it finishes in a reasonable amount of time, send the explain output.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
SELECT performance one bit. It would only
affect transactions that modify the database.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
list. That's
what's preventing 7.3 from doing a good job.
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
, 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
= varchar.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
sort_mem. Multiple aggregates take
more RAM to process in a hashtable style ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
the planner recognizes that the new
index matches the ORDER BY request). Then you should get a plan with
a much smaller cost coefficient for this step.
regards, tom lane
PS: does server_id really need to be NUMERIC? Why not integer, or at
worst bigint
condition has to use stable
or immutable functions. By marking oneshot_random() stable, you
essentially say that it's okay to evaluate it only once per query,
rather than once at each row.
regards, tom lane
---(end of broadcast
Rob Nagler [EMAIL PROTECTED] writes:
Are there plans for explicit hints to the planner?
Personally, I'm philosophically opposed to planner hints; see previous
discussions in the archives.
regards, tom lane
---(end of broadcast
) how many tuples a given WHERE predicate resolves
to.
Why is that better than our existing implementation of column statistics?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map
into a range query:
where begin_time ... AND begin_time 'infinity';
See the archives for more discussion.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
.
Will that increase any throughput?
Probably not...
Also, doesn't flash memory have a very limited lifetime in write cycles?
Using it as WAL, you'd wear it out PDQ.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you
business. See, eg, the thread starting here:
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http
that.
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
. But I'm slightly concerned about changing the
semantics of existing queries. If someone can produce proof that this
is allowed (or even better, required) by the SQL spec, it'd be easier...
regards, tom lane
---(end of broadcast
with this afternoon was
removing the int8-and-int4 comparison operators from pg_operator.
It works as far as making int8col = 42 do the right thing, but I'm
not sure yet about side-effects.
regards, tom lane
---(end of broadcast)---
TIP 4
that the disk throughput triples during
the checkpoint.
Hm, better make sure the log includes some indication of when
checkpoints happen.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
heard of
before ;-).)
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Mary Edie Meredith [EMAIL PROTECTED] writes:
Is there any option to remove the cost numbers from the plan so we can
just use diff to automate the plan comparisons?
No, but a few moments with sed or perl should get the job done for you.
regards, tom lane
to disk. The performance
differential may thus be coming at the expense of reliability. If you
run Postgres with fsync off, does the differential go away?
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off
that. You could hit this even with ~100-column
tables if you try to select all columns from a join of two or more.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http
promise that doing so wouldn't break things.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
a drag on performance.
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
, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
dunno whether the TPC rules allow for
significant manual rewriting of the given query.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
to cancel the query after 10 min.
Force use of the indexes is not always an answer to performance issues.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
not normally
much need for apps to be explicitly aware of the column type names.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
to see.
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
nothing, there's a configuration problem with the pg-perform mail
list or your subscription or something. Talk to Marc (scrappy at
hub.org) about identifying and fixing the issue.
regards, tom lane
---(end of broadcast
and user_account.user_role_id.
The trick is to apply those before joining any other tables.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
, but
there it is...
/digression
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
are a couple of orders
of magnitude smaller than they actually are. Certainly the estimated
sizes of the joins are way off :-(
If you did analyze, it might help to increase the statistics target and
re-analyze.
regards, tom lane
---(end of broadcast
it.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
the index in question?
I suspect it is true that REINDEX locks more than it needs to, but we
should tread carefully about loosening it.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
other transactions should be
included in your count.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
... and it would give the wrong answers. Unless the cache is somehow
snapshot-aware, so that it can know which other transactions should be
included in your count.
The cache is an ordinary table, with xid's on every row. I meant
Bruce Momjian [EMAIL PROTECTED] writes:
It doesn't seem complex enough for a separate TODO.detail item.
I thought it was, if only because it is so easy to think of wrong
implementations.
regards, tom lane
---(end of broadcast
not, even before you look at
the performance issues.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
I think that's not happening, conditionally or otherwise. The atomicity
problems alone are sufficient reason why not, even before you look at
the performance issues.
What are the atomicity problems of adding a create/expire xid
.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
are till it's
looked. Also, VACUUM is the data collector for the free space map,
and so it is also charged with finding out how much free space exists
on every page.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you
is
concerned, but the thing that's needed to make it really useful is
to prevent caching of seqscan-read pages in the kernel disk buffers.
I don't know any portable way to do that :-(
regards, tom lane
---(end of broadcast
.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
, and this isn't
counted in EXPLAIN's runtime measurement. There's no claim anywhere
that the results of such an evaluation would be saved for other plans.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list
.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
to get that query to use an index?
Coerce the constants to bigint, for starters. However, a query that is
selecting almost 10% of the table, as your example is, probably
*shouldn't* be using an index.
regards, tom lane
---(end of broadcast
the memory mapped
into your address space, it's hard to believe that it matters how you
got hold of it.
In any case, mmap doesn't have the semantics we need. See past
discussions.
regards, tom lane
---(end of broadcast
+ fi
fi
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Chris Faulkner [EMAIL PROTECTED] writes:
I am seeing this message in my logs.
bt_fixroot: not valid old root page
That's not good. I'd suggest reindexing that index.
regards, tom lane
---(end of broadcast)---
TIP 2: you
Josh Berkus [EMAIL PROTECTED] writes:
Unless there's a way to find it in the compiled source?
See pg_controldata.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
1 - 100 of 3915 matches
Mail list logo
