improved since 2005 - their performance was
improved quite a bit in 9.0. Here's a more recent analysis:
http://www.depesz.com/index.php/2010/06/28/should-you-use-hash-index/
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent
to introduce on-disk bitmap indexes to Postgres.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
doesn't pay
the considerable overhead of that instrumentation across the board).
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
that will block a select statement's AccessShare
lock.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
equivalent.
http://www.postgresql.org/docs/current/static/functions-subquery.html
See the notes about NULL under IN.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list (pgsql-performance
This has been reposted to this list from the pgsql-hackers list, at
the request of Josh Berkus. Hopefully there will be more interest
here.
-- Forwarded message --
From: Peter Geoghegan pe...@2ndquadrant.com
Date: 29 July 2012 16:39
Subject: Help me develop new commit_delay advice
way to monitor query
execution costs on earlier versions, I think that I'll probably have
new information about that for my talk at Postgres Open.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance
On 6 September 2012 04:20, Greg Smith g...@2ndquadrant.com wrote:
On 08/02/2012 02:02 PM, Peter Geoghegan wrote:
I dug up what I wrote when trying to provide better advice for this circa
V8.3. That never really gelled into something worth publishing at the time.
But I see some similar
into a column, analyzed and grouped by
that.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
costs essentially nothing to maintain, and simply
represent an ongoing obligation for ANALYZE to provide statistics for
an expression?
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list
* Postgres is
doing with memory, but need to summarise it usefully.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription
that for years, so it's already something that we've effectively
committed to.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On 21 November 2012 15:21, Andrew Dunstan and...@dunslane.net wrote:
And I continue to think that spelling it OFFSET 0 is horribly obscure.
I'm not sure that it's any more obscure than the very idea of an
optimisation fence.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL
/commands/analyze.c (IBM
Research Report RJ 10025 is referenced there).
The general advice here is:
1) Increase default_statistics_target for the column.
2) If that doesn't help, consider using the following DDL:
alter table foo alter column bar set ( n_distinct = 5.0);
--
Peter Geoghegan
a functional index on length(discussion_post.id)?
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
(essentially, the internal representation that the
rewriter stage processes).
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
to disk when there's a clean
shutdown. pg_stat_statements is similar to the statistics collector
here.
Why are you posting this to the -performance list?
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
histograms of lower and upper bounds for range
types, and the fraction of empty ranges.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
/wiki/Hint_Bits
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
standard packages, I'm afraid.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
, it is crucially important to consider power saving
features in most benchmarks these days, where that might not have been
true a few years ago. The CPU scaling governor can alter the outcome
of many benchmarks quite significantly.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list
free. Hashing is really
cheap when the CPU is bottlenecked on memory bandwidth.
If you're interested, download the patch, and enable the debug traces.
You'll see HyperLogLog accurately indicate the cardinality of text
datums as they're copied into local memory before sorting.
--
Regards,
Peter
be
crazy).
Streaming algorithms like HyperLogLog are very recent ideas, as these
things go. I wouldn't be all that discouraged by the fact that it
might not have been put to use in this way (for database statistics)
by somebody before now.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance
,
and things like that. I tend to think that that's a common enough
use-case.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
within arrays are
keys for our purposes, and these are often used for tags and so on.
But Strings that are the key of an object/pair are much less useful to
index, in my estimation.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
to complete it. That much is very
clear.
If you're really worried about these costs, I suggest enabling
trace_sort locally, and monitoring the progress of this sort in the
logs.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
settings are unlikely to help unless
they result in a fully internal sort.
There is evidence that the heap that tuple sorting uses benefits from
*lower* settings. Sometimes as low as 64MB.
We're working to make this better in 9.6.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-performance mai
f you're arguing for the idea that B-Trees should reliably keep
tuples in order by a tie-break condition, that seems difficult to
implement, and likely not worth it in practice.
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to yo
ch, but
it's probably better to focus on duplicate storage or even leaf page
compression, as Stephen mentioned.
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
cardinality
inputs, too. I bet that was a factor here.
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
lly confuses us is
> that this job has been running for years with no issue remotely approaching
> this one. We are also using pgpool.
Did you happen to notice that this occurred when you upgrading point
release? If so, what version did you move from/to?
--
Peter Geoghegan
--
Sent via pgsq
mizations are closely
intertwined things, and the lack of clarity on how they all fit
together is probably holding back an implementation of any one of
them.
[1]
https://www.postgresql.org/message-id/flat/20160524173914.GA11880%40telsasoft.com#20160524173914.ga11...@telsasoft.com
--
Peter Ge
PDATEs?
I ask these questions because I think it's possible that this is
explained by a regression in 9.5's handling of index bloat, described
here:
http://postgr.es/m/CAH2-Wz=sfakvmv1x9jh19ej8am8tzn9f-yecips9hrrrqss...@mail.gmail.com
I'm trying to track down cases where this could be an issu
et Postgres to not do a
> quick sort here?
I would like that too. There is a patch that does what I think you're
describing, but it seems to be in limbo:
https://commitfest.postgresql.org/11/409/
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or
want to learn more about the B-Tree code, I suggest that you
start by looking at the code for contrib/amcheck.
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
allowing it. We can instead just pretend that it's shorter, knowing
that upper levels don't contain useful information.
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
faster machine but
> some non-C locale on the slower. strcoll() is pretty darn expensive
> compared to strcmp() :-(
strcoll() is very noticeably slower on macOS, too.
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to yo
39 matches
Mail list logo
