keep using those.)
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
instantaneously.
If you're concerned mostly with this type of query then a 2-column index
on (conversation_id, created_at) would serve your purposes nicely. You
could likely even dispense with the separate index on conversation_id
alone.
regards, tom lane
--
Sent via pgsql
by teaching the planner to drill down into CTEs to find
variable referents, as it already does for subquery RTEs (cf
examine_simple_variable in selfuncs.c). I'm not sure if your case is
similar or not --- you didn't provide any useful amount of detail.
regards, tom lane
think. The main problem is in
the sub-select: 9.0 isn't able to index-optimize a MAX() across a
partitioned table, for lack of MergeAppend, so you end up scanning lots
of rows there. 9.1 or 9.2 should be better.
regards, tom lane
--
Sent via pgsql-performance mailing list
on the
referencing side. But that only hurts when doing UPDATEs/DELETEs of
referenced-side keys, which as far as I gathered was not the OP's
scenario.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription
.
This is actually an abbreviation for \dtisv+, which is a completely
different command from \d table. You can use something like
\dt+ table-pattern to get a display of the above form for a subset
of tables. I agree it ain't too consistent.
regards, tom lane
--
Sent via
that EDB's porting tools
evidently don't do this automatically (I infer from the reference to
PPAS that the OP is using EDB ...)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
=?UTF-8?B?bm9ib2R5IG5vd2hlcmU=?= devn...@mail.ua writes:
[ all postgres processes seem to be pinned to CPU 14 ]
I wonder whether this is a benefit of sched_autogroup_enabled?
http://archives.postgresql.org/message-id/50e4aab1.9040...@optionshouse.com
regards, tom lane
:
http://www.postgresql.org/docs/9.2/static/indexes.html
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
trouble. We've seen people
remove essential details before while trying to anonymize their query.
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
would eliminate
a lot of possibilities.
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
the
database on a filesystem that doesn't scale well to lots of files in one
directory. If that's the explanation, the reason the 8.3 installation
was okay was likely that it was stored on a more modern filesystem.
BTW, please keep the list cc'd on replies.
regards, tom lane
Nikolas Everett nik9...@gmail.com writes:
On Thu, Dec 27, 2012 at 4:33 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Nikolas Everett nik9...@gmail.com writes:
We straced the backend during the explain and it looked like the open
commands were taking several seconds each.
Kind of makes me wonder
index has accumulated less
bloat, and thus has a perfectly justifiable cost advantage.
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
.
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
of partitions; you're
going to be bleeding performance in a lot of places if you insist on
doing 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
.)
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
to use the existence of FK
constraints to improve plans, but I don't believe any such thing is
in the code today.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
to 30 minutes.
It's got us rather puzzled. Has anyone seen anything like this?
Maybe the kernel is auto-nice'ing the process once it's accumulated X
amount of CPU time?
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
configuration. Have you tried basic disk-speed
benchmarks? (Perhaps there's an equivalent of bonnie++ for windows.)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
of
these as an optimization fence. (The clauses would still work as an
opt fence, you'd just not see any Limit node in the final plan.)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription
Kevin Grittner kgri...@mail.com writes:
Tom Lane wrote:
1.35ms out of what?
Without the limit node the runtimes (after priming the cache)
were:
1.805, 2.533
1.805, 2.495
1.800, 2.446
1.818, 2.470
1.804, 2.502
The first time for each run is Total runtime reported by EXPLAIN
that small values of random_page_cost necessarily
decrease the apparent advantage of index-only scans. If you think 3.5
is an insanely high setting, I wonder whether you haven't driven those
numbers too far in the other direction to compensate for something else.
regards, tom lane
the plan. The planner would possibly have done the last
join step differently if it had had a better rowcount estimate, but even
if that were free the query would still have been 7 seconds (vs 8.5).
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql
that, though, it's not real clear to me why the plancache
changes would have affected the speed of EXECUTE at all --- the whole
point of that command is we don't cache a plan for the query.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance
indeed have no join
partners, but there are a small number with a large number of partners.
The statistics might miss these, if so.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
to know if applying bf01e34b556
helps the OP's example.
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
Trying to interpret this, does this mean that the autovacuum process is
holding a lock which is required tn order to complete the select
query?
Possibly. Looking into the pg_locks view would tell you more.
regards, tom lane
--
Sent via pgsql-performance mailing list
such a thing, there's no need to break the historical
usage.
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 typically wanted for only specific subqueries.
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
.
Throwing errors would likely prevent you from reaching all parts of your
application, thus preventing complete testing. Much more sensible to
just log such queries.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes
on the
CTE.
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
in WITH queries if they want
to be sure there's an optimization fence?
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
isn't C you'll need to use a
varchar_pattern_ops index.)
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
example
implies, the planner certainly ought to try to use a compatible index.
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
note_sets_parent_id_idx btree (parent_id) WHERE parent_id IS NOT NULL
Apparently 9.2 is less bright than 9.1 about when it can use a partial
index. I'm not sure where I broke that, but will look.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql
functions in both cases,
it's not obvious where the optimizer could get any purchase that way.
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
like that,
but I'm not convinced that will really make the estimates better, mainly
because ndistinct is none too reliable itself.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
debatable for SELECT; there are some advantages
to providing a fence this way but there are definitely downsides too.
I could see adjusting that definition in the future, as we get more
experience with use of CTEs.
regards, tom lane
--
Sent via pgsql-performance mailing
?
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
a
nestloop-with-inner-indexscan join there, else it would have picked that
type of plan. Why it's failing is as yet unclear.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
underestimate of the number of
groups implied by the GROUP BY clause. Do you have up-to-date
statistics for the source table?
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
Denis soc...@gmail.com writes:
Tom Lane-2 wrote
Hmmm ... so the problem here isn't that you've got 2600 schemas, it's
that you've got 183924 tables. That's going to take some time no matter
what.
I wonder why pg_dump has to have deal with all these 183924 tables, if I
specified to dump
;a=commitdiff;h=ca2d6a6cef5740b29406980eb8d21d44da32634b
but I'd still want to see a test case to be sure. In any case,
it's not clear what's the critical difference between the fast and
slow versions of the query.
regards, tom lane
--
Sent via pgsql-performance mailing list
or three tables before you can even join to the main
fact table - and those tables don't even have the virtue of being small.
That's never going to perform well.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes
provide EXPLAIN ANALYZE output for that query?
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
, it would be pure luck if you got a
good query plan for an example like this. Maybe that and/or other
parameter settings didn't get transposed to the 9.2 installation.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
test case to duplicate these
results? I'm prepared to believe there's some sort of planner
regression involved here, but we'll never find it without a test case.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
Rodrigo Rosenfeld Rosas rr.ro...@gmail.com writes:
Em 06-11-2012 17:24, Tom Lane escreveu:
Can you put together a self-contained test case to duplicate these
results? I'm prepared to believe there's some sort of planner
regression involved here, but we'll never find it without a test case
...
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
a single table
with no WHERE restriction; (2) all produce the same column datatypes;
and (3) not have any volatile functions in the SELECT lists. I might be
missing something relevant to the OP's case, but it's hard to tell
without a concrete example.
regards, tom lane
. These two seem a bit odd, and certainly not
terribly well matched to this query.
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
from here. I get about
the same results from HEAD, 9.2 branch tip, or 9.1 branch tip.
So I'm wondering exactly what 9.1 version you're using, and also
whether you've got any nondefault planner cost parameters.
regards, tom lane
--
Sent via pgsql-performance mailing list
useful data.)
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
some fraction-of-a-histogram-bin's worth of
duplicates, but that would make the results worse for some people.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
-counting.)
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
on the named columns?
It's not particularly (not that you've even defined what you think
optimistic is, much less mentioned what baseline you're comparing to).
I tried your example on HEAD and I got what seemed pretty decent
rowcount estimates ...
regards, tom lane
--
Sent via
the planner
from assuming that the carried-forward result is small; so maybe we
should use something larger than 10.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
Shaun Thomas stho...@optionshouse.com writes:
Yet there's only one global setting for random_page_cost, and
seq_page_cost, and so on.
We've had tablespace-specific settings for those for some time.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql
)?
Implementation restriction - we don't yet have a way to match index-only
scans to expressions.
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
Thom Brown t...@linux.com writes:
On 18 October 2012 17:44, Tom Lane t...@sss.pgh.pa.us wrote:
Thom Brown t...@linux.com writes:
And as a side note, how come it's impossible to get the planner to use
an index-only scan to satisfy the query (disabling sequential and
regular index scans
really seem to
care about is more intelligence about making use of expression indexes
to avoid recalculation of the expression --- something you'd not get
from a stats-only feature.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance
to credit
that yesterday you were just under the limit and today you're just
over even though nothing changed.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
on the parent table (stream_store) would help.
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
the my_date column?
No. The cast seems rather pointless though ...
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
Sergio Gabriel Rodriguez sgrodrig...@gmail.com writes:
On Thu, Oct 11, 2012 at 7:16 PM, Tom Lane t...@sss.pgh.pa.us wrote:
It's pretty hard to say without knowing a lot more info about your system
than you provided. One thing that would shed some light is if you spent
some time finding out
that is worth fixing here, it's the number of server
roundtrips being used ...
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
also be done without that. Whatever your cutoff
time is can be expressed as a timestamp *with* tz.
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
in which process,
pg_dump or the connected backend?
Also, how many large objects is that? (If you don't know already,
select count(*) from pg_largeobject_metadata would tell you.)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
is available for a day or so for more tests if
anyone has suggestions.
It would be nice to see similar tests done with 9.2. 8.4 is kind of old
news as far as server performance is concerned.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql
the same with or without the CTE
layers. I'm not sure it's worth the trouble though --- I'm dubious that
people would use a CTE for cases that are simple enough for the stats
estimates to be worth anything.
regards, tom lane
--
Sent via pgsql-performance mailing list
Andrzej Zawadzki zawa...@wp.pl writes:
I have no idea whats wrong. Looks like planer bad decision.
[ counts... ] You've got nine base relations in that query. I think
you need to increase from_collapse_limit and/or join_collapse_limit.
regards, tom lane
--
Sent via
Andrzej Zawadzki zawa...@wp.pl writes:
On 08.10.2012 16:52, Tom Lane wrote:
[ counts... ] You've got nine base relations in that query. I think
you need to increase from_collapse_limit and/or join_collapse_limit.
Bingo! Thank you!
But... looks like in 9.0 this worked differently or option
that the planner won't re-sort the rows coming from
the sub-select, unfortunately.
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
Andres Freund and...@2ndquadrant.com writes:
On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote:
There's no guarantee that the planner won't re-sort the rows coming from
the sub-select, unfortunately.
More often than not you can prevent the planner from doing that by putting a
OFFSET 0
very meaningful here; think
about FOR UPDATE switching its attention to updated versions of rows.
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
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
outputs you're actually
getting, rather than assuming others will get the same thing.
regards, tom lane
(PS: it does seem that HEAD has got some kind of issue here, because
it's picking a plain not bitmap indexscan. I'll go look at that.
But I don't see that misbehavior
.
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
part of the table. Providing a constant limit
for just one side wouldn't fix 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
alone in 9.2.)
In a real application you could possibly reduce the problem by
rearranging operations, but that would be cheating of course for a
benchmark.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes
to dump, but I'm not sure that's
relevant to your situation, because before 9.0 pg_dump didn't treat
blobs as full-fledged database objects. You wouldn't happen to be
trying to use a 9.0 or later pg_dump would you? Exactly what 8.4.x
release is this, anyway?
regards, tom lane
Sergio Gabriel Rodriguez sgrodrig...@gmail.com writes:
On Thu, Sep 20, 2012 at 11:35 AM, Tom Lane t...@sss.pgh.pa.us wrote:
You wouldn't happen to be
trying to use a 9.0 or later pg_dump would you? Exactly what 8.4.x
release is this, anyway?
Tom, thanks for replying, yes, we tried
.
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
changing 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
, not the underlying content column, that needs its statistics
target adjusted.
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
Bill Martin bill.mar...@communote.com writes:
Tom Lane t...@sss.pgh.pa.us writes:
He can do it without having to change his schema --- but it's the index
column, not the underlying content column, that needs its statistics
target adjusted.
How can I adjust the statistics target of the index
it?
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
for generating bazillions of notify events per transaction. It won't
help to hack AsyncExistsPendingNotify if dropping the events into the
queue is still too expensive. I am worried about the overall processing
cost here, consumers and producers both.
regards, tom lane
--
Sent via
in total, now
it only takes 3 seconds. Comments?
Shall I commit to master and all supported branches?
Was this applied?
No, we fixed the server side instead.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
, so a multiplier of 0.25 seems right in
hindsight, and that seems to match up roughly right with the mergejoin
cost estimate --- but not knowing the actual table size, there's a lot
of uncertainty here.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql
.
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
as designed here.
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
Christophe Pettus x...@thebuild.com writes:
On Aug 13, 2012, at 7:11 PM, Tom Lane wrote:
The whole thing looks a bit weird to me --- why did it not use a
nestloop join with inner indexscan on charlie? With 7000 rows on the
other side, the estimated cost for that shouldn't have been more than
Josh Berkus j...@agliodbs.com writes:
On 8/15/12 1:51 PM, Tom Lane wrote:
Maybe you had better show us the actual query, and the table/index
definitions. Because it's sure making odd choices here. This seems
like the wrong join order altogether ...
We'll need to do that off-list
think.
The whole thing looks a bit weird to me --- why did it not use a
nestloop join with inner indexscan on charlie? With 7000 rows on the
other side, the estimated cost for that shouldn't have been more than
about 3 ...
regards, tom lane
--
Sent via pgsql
. It is a serious mistake to think that
a seqscan is evil when you're dealing with joining that many rows, btw.
What you should probably be looking for is a hash join plan.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes
Ioannis Anagnostopoulos ioan...@anatec.com writes:
On 06/08/2012 16:34, Tom Lane wrote:
What you should probably be looking for is a hash join plan.
...
Which is a Merge join and not a hash. Any ideas how to make it a hash join?
You might need to ANALYZE the temp table, if you didn't already
might
help.
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
might help.
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
401 - 500 of 3915 matches
Mail list logo
