indexes.
[1]
http://pgeoghegan.blogspot.com/2015/01/abbreviated-keys-exploiting-locality-to.html
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ation isn't acceptable. ON CONFLICT DO UPDATE
should be preferred once 9.5 is released.
[1]
http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgres
ordering by anyway, so you can
just not concatenate the ', ' string (so name_last || name_first), and
it will work as you expect, I believe.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
, based on what you say here, I
think you should actually "ORDER BY name_last, name_first".
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I think it would be really handy if temp_tablespaces were made resilient
against everything going away in the event of a hard crash.
--
Regards,
Peter Geoghegan
was superseded by a new version.
[1]
http://www.postgresql.org/message-id/CAEYLb_UTMgM2V_pP7qnuKZYmTYXoym-zNYVbwoU79=tup8h...@mail.gmail.com
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
as carrying on, because there
is no reason to think that the locale thing can easily be rolled back.
That was my point, in fact.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/
rst
version that happens to have ICU support). I don't like suggesting a
solution that I myself am unlikely to find the time to work on, but in
the long run that's the only sensible approach IMV.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@post
so that the collations simply never
go away, but if that does happen (or if you decide that the changes to
a collation matter for cultural or correctness reasons) then you can
at least detect the change and recover from it reliably.
ICU has some other really nice features, too, but that's ano
r are their collations graven on stone tablets, unlike
> anyone else's?
See my response to Thomas.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
s no unique or exclusion constraint matching the ON CONFLICT
> specification
>
> If anyone knows what I'm doing wrong and how to get this to work, or knows
> that this is not possible to achieve, I'm all ears.
That should work. Are you sure you haven't spelled it "...
ase replication and backup processes.
This belongs on the pgsql-jobs mailing list, not pgsql-general.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
re
complicated than it first appears, if you expect UPSERT to worry about
lock starvation, "unprincipled deadlocks" [1], and other problems like
that.
[1]
https://wiki.postgresql.org/wiki/Value_locking#.22Unprincipled_Deadlocking.22_and_value_locking
--
Peter Geoghegan
--
Se
e that made that untrue in
1981, if only barely [1], but the lesson for me was to take his claims
in this area with a generous pinch of salt.
[1] https://www.cs.cmu.edu/~pavlo/static/papers/stonebraker-ic2e2014.pdf
(See his citation 11)
--
Peter Geoghegan
--
Sent via pgsql-general maili
is far weaker. What specifically do you say is wrong about his
> current claims, and on what facts to you base it?
I'm not the one making overarching conclusions. I'm not trying to
convince you of anything.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@
here is a similar consideration for DO UPDATE. I'm
slightly surprised that you're contemplating just ripping the check
out. Did I miss something?
[1] https://www.postgresql.org/message-id/57ee93c8.8080...@postgrespro.ru
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgs
Vitaly have said, there is
> literally no concurrent update.
I think that you have the right idea, but we still need to fix that
buffer lock bug I mentioned...
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to you
On Wed, Oct 12, 2016 at 1:41 PM, Kevin Grittner wrote:
> Aren't these two completely separate and independent bugs?
Technically they are, but they are both isolated to the same small
function. Surely it's better to fix them both at once?
--
Peter Geoghegan
--
Sent via pgsql-ge
T DO NOTHING code to avoid false positives where we can.
Do you intend to propose a patch to do that?
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ult.
I was under the impression that false positives of this kind are
allowed by SSI. Why focus on this false positive scenario in
particular?
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.o
27;m still not even clear on
whether you are actually arguing that they are special. (Except, of
course, the multi-value case -- that's clearly not okay.)
So, with the fix proposed by Thomas applied, will there be any
remaining false positives that are qualitatively different to existing
false p
erspective.
What are your thoughts on the back-and-forth between myself and Tom
concerning predicate locks within heap_fetch_tuple() path last
weekend? I now think that there might be an outstanding concern about
ON CONFLICT DO NOTHING + SSI here.
--
Peter Geoghegan
--
Sent via pgsql-
rmine
that it would be just fine to use the C locale, since the user isn't
entitled to assume anything about the exact sort order. There are of
course cases where this can make a huge difference.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
ardinality leading attribute, so
this habit works against tuplesort. (Assuming a leading attribute of
pass-by-value type, or with abbreviated key support.)
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Mar 14, 2016 at 12:28 PM, Peter Devoy wrote:
> Is there a reason DO NOTHING was not developed for use with RETURNING?
I don't know what you mean. It should work fine with RETURNING.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
er way. Maybe ON CONFLICT DO SELECT where the select
> operates over the target row.
Seems reasonable.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
scheme-like syntax, and the storage model would be properly relational (eg no
> duplicate rows).
Have you heard of QUEL?
See https://en.wikipedia.org/wiki/QUEL_query_languages
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your sub
antage of abbreviated keys?
You need to use an ICU collation. It must be a per-column collation,
as you cannot currently use ICU for an entire database. (This
limitation should be removed in the next release or two.)
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsq
specify collation when using ORDER by on that column
> for index and abbreviated keys to be used?
Only if you didn't define the column with a per-column collation initially.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
On Mon, Nov 13, 2017 at 12:40 PM, Peter Geoghegan wrote:
>> Do I have to explicitly specify collation when using ORDER by on that column
>> for index and abbreviated keys to be used?
>
> Only if you didn't define the column with a per-column collation initially.
BTW, if y
hing in particular gets faster, because there are many
performance enhancements added to a release.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
'll help with that, even though the leading column might be
low cardinality.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 7 October 2010 14:45, Juan Sueiro wrote:
> Any advices would be really appreciated.
You should upgrade to a version of Postgres that is currently
supported right away.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to y
ot re-order columns
on purely aesthetic grounds.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
;s why the postgres database exists.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
low counting is an idiosyncrasy of postgres.
http://wiki.postgresql.org/wiki/Slow_Counting
To get the top 10%:
SELECT * FROM table LIMIT(SELECT (COUNT(*) * 0.1)::integer FROM table)
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make cha
e execution of all triggers?
>
The update will acquire a row level lock on rowcount for the
TG_RELNAME tuple without you doing anything else.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
You need to vacuum more aggressively.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pshot' returns a 64 bit
> value. I don't get it. All I want to is make sure I skip over any
> rows that are newer than the oldest currently running transaction.
> Has nobody else run into this before?
If I understand your question correctly, you want a "gapless" PK:
some hard limit on the number of rows viewable in a
table? Would that really be so terrible?
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
de: the number of rows returned was not known in advance
of scrolling down to the last one. So you couldn't visualise the size
of the record set based on the size and relative position of the
scrollbar.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@p
hat platform by using
proportionally more file system/OS cache.
However, it is worth acknowledging that there has been some excellent
work towards getting Postgres to work well on Windows, which it now
does. I can personally attest to that.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mai
s to all of us that what you're doing is far riskier
than just upgrading to 8.2.18, and makes absolutely no sense. There
are no behavioural differences between 8.2.3 and 8.2.18. We are
*extremely* conservative and disciplined about release management, so
that users don''t have t
my Irish locale Windows XP, but it's probably something
else for you).
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
eplicate the database tables?
>
>
>
> Thanks in advance.
Yes. Look at dbi-link.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
gt;
> Or perhaps I do not understand your question.
>
Uh, no, dblink is for connecting to a PostgreSQL database from within
another. You want dbi-link.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
uot; for details.
Type "help" for help.
postgres=# show client_encoding;
client_encoding
-
UTF8
(1 row)
postgres=#
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Just use the python 2.6 installer for windows.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ast
on more than one occasion. Perhaps it wasn't stressed too much, but
certainly it was treated as a greater than negligible issue:
http://blog.hagander.net/archives/73-PostgreSQL-vs-64-bit-windows.html
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@po
, so the memory requirement is
slightly increased. This can cause a slight drop in performance. On
the other hand, having twice as many registers and having the ability
to do 64-bit integer calculations in a single instruction will often
more than compensate. The net result is that a 64-bit applicat
it's just pseudo-code.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
hon function in 9.0 and it will be interpreted
as an array at the SQL call site. You cannot in prior versions.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
; queue.
> Is it possible to implement? Or there is only one way - send queries
> one-by-one?
I suggest you take a look at libpqxx's pipeline class.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ist)
that will be called back asynchronously like a signal handler.
Asynchronous command processing just exists as a way to keep a GUI
responsive and things like that, because PQExec() blocks.
Maybe it would help if you stepped back and described your problem in
broader terms.
--
Regards,
Peter
uot;server" client. Having one
process that itself has up to a thousand clients but uses only one PG
connection is a very questionable approach - the single database
connection is certain to become a bottleneck.
and
3. Use a connection pooler.
--
Regards,
Peter Geoghegan
--
Sent via p
utes -
> it bocomes to be a very good time.
Databases are optimized for throughput, not latency. It isn't in
question that there would be less latency if we could parallelise the
queries. What is in question is:
1. Whether or not it matters.
2. Whether or not that's possible, given t
a guide to choosing hardware for Postgres, I can highly
recommend Greg Smith's new book, "Postgresql 9 High performance".
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
This is an very common and well understood problem. Take a look at this:
http://www.varlena.com/GeneralBits/130.php
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
binary, let alone storing it.
You should just use a generic escaping function. libpq has
PQescapeByteaConn(), for example.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
or as an optimisation, unless you
had the unusual situation of having very static data in the table.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
will
reduce index fragmentation. OTOH, indexes for static data can have
their fillfactors increased to 100% from the default of 90% without
consequence.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
s probably a pain. I think that plperl.dll simply wants to link
to a major version of perl that you don't have. It's a matter of
finding out which and installing it.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make chan
om pg_tablespace;
-[ RECORD 1 ]---
oid | 1663
spcname | pg_default
spcowner| 10
spclocation |
spcacl |
spcoptions |
-[ RECORD 2 ]---
oid | 1664
spcname | pg_global
spcowner| 10
spclocation |
spcacl |
spcoptions |
--
Regards,
Peter Geogheg
What version of PostgreSQL are you using? sort_mem is now called
work_mem (to better reflect the reality that it isn't just used in
sorting, I think), and has been for some time.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Ser
I think that this blogpost touches upon the issue you're facing:
http://it.toolbox.com/blogs/database-soup/partition-at-insert-time-a-smart-mistake-44294
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
--
Sent via pgsql-ge
The postmaster.pid file shows the pid of the postmaster. The file
shouldn't exist when the postmaster isn't running, so it should be
safe to delete. Its presence does indicate that postgres was
improperly shutdown though.
--
Peter Geoghegan http://www.2ndQuadrant.com/
ch as statements executed within functions are
tracked.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
h
is the nature of these things. If it was something that came up a lot,
it would surely have been settled by standardization years ago.
If OS vendors are not going to give us a standard API for versioning,
we're hosed. I thought about suggesting that we hash a strxfrm() blob
for about 2 m
ound B-Tree indexes on text.
[1] https://wiki.postgresql.org/wiki/Todo:ICU
[2] http://userguide.icu-project.org/collation/architecture#TOC-Versioning
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
erly fix the
problem. This is a problem that is well understood, and anticipated by
the Unicode consortium.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Aug 20, 2014 at 1:53 PM, Larry White wrote:
> Is there anyway to index a subset of the data in a JSONB column? I'm
> thinking of something like declaring certain paths to be indexed?
Yes. See the expression index example in the jsonb documentation.
--
Regards,
Pete
On Sun, Aug 24, 2014 at 7:05 PM, Huang, Suya wrote:
> It’s my test DB, the version is PostgreSQL 9.4beta2. I’m testing with nested
> hstore feature.
Really? Nested hstore only made it into that version as jsonb, which
you're clearly not using here.
--
Regards,
Peter Geoghegan
-
ing opclass can support. But, why
should it be supported? That's a very fuzzy criteria to search on.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
using 64-bit xid values is that they
require more storage than 32-bit values. There is a patch floating
around that makes it safe to not forcibly safety shutdown the server
where currently it is necessary, but it doesn't work by making xids
64-bit.
--
Regards,
Peter Geoghegan
--
S
possibly a quite complex expression. However, it's
not clear what behavior is expected here, since multiple SKUs may
appear per row. If you had a table with "products", with a jsonb
column, and one row per product, you could then usefully extract at
most one SKU per row, and tha
On Wed, Sep 24, 2014 at 2:01 PM, Paul Ramsey wrote:
> If I build an index on the same table using the internal quad-tree ops, and
> use their operator, I do get an index scan.
What about when enable_seqscan = off?
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list
esql.org/action/patch_view?id=1462
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Dec 9, 2014 at 5:46 PM, Peter Geoghegan
wrote:
> I'm currently trying to fix this across the board [1], but my first
> suggestion is to try enabling log_temp_files to see if external sorts
> can be correlated with these stalls.
See also:
http://www.postgresql.
fixed in PostgreSQL 9.4, so that query texts can be of
virtually unlimited size and still be stored. Otherwise, it depends on
your track_activity_query_size setting.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscript
ally, I think the version on pgfoundry is unmainted.
I'd look here instead:
https://github.com/snaga/xlogdump/commits/master
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jun 14, 2013 at 11:55 AM, Andreas wrote:
> How can I get more memory for PG on openSUSE 12.3 ?
http://www.postgresql.org/docs/9.2/static/kernel-resources.html
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to y
https://github.com/snaga/xlogdump
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ion or limit to do it.?
I am not a lawyer and this isn't legal advice. That said, I have ever
reason to believe that the licensing is the most liberal practically
possible. Distributing Postgres with your proprietary application
should not be a problem.
--
Regards,
Peter Geoghegan
--
Sent
f an UPDATE than it is to back out of an
INSERT. If you're really interested, search through the -hackers
archives from around April of 2015.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postg
t; and found all tables’ id were reset to 1.
I've heard of this happening before. I never determined what the cause was.
--
Peter Geoghegan
VMware vCenter Server
https://www.vmware.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your s
lemented. Index scans will on average have a much more random
access pattern than what is typical for bitmap heap scans, making this
optimization more compelling, so hopefully someone will get around to
this.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.or
prefetching heap pages mattering a lot less for a
primary key index, where there is a strong preexisting correlation
between physical and logical order, while also mattering a lot more
than what I describe in other cases. I suppose that you need both.
--
Peter Geoghegan
--
Sent via pgsql-gene
imagine
the optimization saving certain queries from consuming a lot of memory
bandwidth, as well as saving them from pinning and locking the same
buffers repeatedly.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Jun 19, 2017 at 4:51 PM, Peter Geoghegan wrote:
> This would make only the first lookup for each distinct value on the
> outer side actually do an index scan on the inner side. I can imagine
> the optimization saving certain queries from consuming a lot of memory
> bandwidth
ation in the joined-on column with
MERGE). But, MERGE would be faster for bulk loading, which is what
MERGE is good for.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ent to SQL, and so wouldn't help with this general problem. Quel
wasn't successful because it was only somewhat better than SQL was at
the time.
This is a conversation that I had a few times when I worked for
Heroku, with coworkers that weren't on the database team. They asked
simil
hile? Are these unique indexes or not? Do you have a
workload with many UPDATEs?
I ask all 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-yecips
break down, in terms of how much each individual
index grows in size?
You say that the problem is with both indexes and tables. How much of
this is table bloat, and how much is index bloat?
Thanks
[1]
https://postgr.es/m/CAH2-Wz=sfakvmv1x9jh19ej8am8tzn9f-yecips9hrrrqss...@mail.gmail.com
--
Pet
On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan wrote:
> On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote:
>> We've added duplicate indexes and analyzing, however the new indexes are
>> still ignored unless we force using enable_seqscan=no or reduce
>> random
ture you describe. Subtleties like this could easily be
missed.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
?
That's quite possible. An ON CONFLICT's UPDATE accepts a WHERE clause,
which can reference both existing and excluded tuples. That WHERE clause
can back out of the UPDATE based on whatever criteria you like.
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgs
e a pattern
> related to application business processes but we are at a loss as to how
> this could happen.
You've given no details at all. What business pattern? What does the
index and table look like?
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@post
s much CPU as the master's backend.
>
> What am I missing to reproduce the problem?
Just a guess, but do you disable autovacuum on your dev machine? (I know I do.)
It's possible that this is relevant:
https://postgr.es/m/CAB-EU3RawZx8-OzMfvswFf6z+Y7GOZf03TZ=bez+pbqx+a4...@ma
On Mon, Oct 9, 2017 at 12:08 PM, Christophe Pettus wrote:
> Suggestions on further diagnosis?
What's the hot_standy_feedback setting? How about
max_standby_archive_delay/max_standby_streaming_delay?
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql
1 - 100 of 226 matches
Mail list logo
