On 10/8/17 3:37 PM, Tom Lane wrote:
Jim Nasby 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 anything.
On 10/8/17 3:02 PM, Jim Nasby wrote:
-> Index Scan using bdata_filed_departuretime on bdata_forks
(cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777
rows=508 loops=1)
-> Index Scan using bdata_filed_departuretime on bdata_forks
(cost=0.57..14894236.06 rows=1 wi
On 10/8/17 2:34 PM, Tom Lane wrote:
Jim Nasby writes:
I've got a query that's regressed from 9.4 to 9.6. I suspect it has
something to do with the work done around bad plans from single-row
estimates.
Why has this indexscan's cost estimate changed so much?
->
I've got a query that's regressed from 9.4 to 9.6. I suspect it has
something to do with the work done around bad plans from single-row
estimates. There's a SeqScan happening even though the join is to the PK
of bd_ident. Full plans are at [1,2,3], but here's the relevant bits...
9.4:
->
sensitive PG is to IO
latency a larger block size could theoretically mean a big performance
improvement in some scenarios.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
go wrong, especially if you're rolling your own tool.
The complexities around PITR are why I always recommend also using
pg_dump on a periodic (usually weekly) basis as part of your full DR
strategy. You'll probably never use the pg_dump backups, but (in most
cases) they're a
Z <>
8192.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
ll. I didn't realize that SELECT ... (SELECT
slow_stable_function()) was a thing until reading this thread.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TR
replacement will be *extremely* expensive. Some operations will
use a different buffer replacement strategy, so you might be OK if some
of the database doesn't fit in shared buffers; that will depend a lot on
your access patterns.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Ex
On 10/27/16 8:37 PM, Filip Rembiałkowski wrote:
Does it make sense to ask on postgis-users list?
Yes. I suspect that the reason Buffers: shared hit is so high is because
of something st_distance_sphere() is doing.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in
the DISTINCT,
this will probably get a better plan. If you can't do that then you
could try changing the JOIN to an IN:
SELECT ... FROM sample
WHERE version_id IN (SELECT version_id FROM sample)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Archi
to 9.6, that had to happen ever 2B
transactions. With 9.6 there's a freeze map, so if a page never gets
dirtied between vacuum freeze runs then it doesn't need to be frozen.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and Postgre
s per row
(plus indexes) arrays give you essentially zero overhead per row.
There's no code yet, but a few of us have done testing on some real
world data (see the google group referenced from the README).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Ana
OMMIT; (or rollback...)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pgsql-p
what
you wanted... weren't you basically suggesting storing one line per row?
There's certainly other options if you want full tracking of every
change... for example, you could store every change as some form of a
diff, and only store the full document every X number of changes.
nly want partitioning. The good news is that
you can mostly hide partitioning from the application and other database
logic, so there's not a lot of incentive to set it up immediately. You
can always do that after the fact.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Expe
see how well it works.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pg
On 9/23/16 12:59 PM, phb07 wrote:
Le 21/09/2016 à 23:42, Jim Nasby a écrit :
On 9/12/16 1:05 PM, phb07 wrote:
The drawback is the overhead of this added ANALYZE statement. With a
heavy processing like in this test case, it is worth to be done. But for
common cases, it's a little bit expe
hinks it's going to get 100 rows when it's only getting a few.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-946
make a decision on whether to ANALYZE based on that, possibly by looking
at pg_stat_all_tables.n_mod_since_analyze.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TR
).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pgsql-performance
would help here.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pgsql
if it's
still a problem or not.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance m
partition the table so non-current rows
don't live with current ones. I'm not a fan of mixing history tracking
in with the main table, because it leads to problems like this.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and Postg
happens parallel in over a dozen sessions
b01?
4. truncate t01
Huh??
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
these plans originated from the
same piece of SQL?
plpgsql runs all it's stuff via SPI, which can replan queries. So yes, I
think it's necessary to deal with that.
That said, if we only kept the most expensive X plans from a given
function, that could handle both cases.
--
Jim N
each column
into an array or storing an array of a composite type. [1] is exploring
those ideas right now.
You could also try cstore_fdw. It's not a magic bullet, but it's storage
will be much more efficient than what you're doing right now.
[1] https://github.com/ElephantSta
t aggregated before presentation (or
with non-text output formats we could provide the raw data). Or just
punt and don't capture the data unless you're using an alternative
output format.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Archit
On 7/19/16 3:10 PM, Tom Lane wrote:
Jim Nasby writes:
On 7/5/16 7:14 AM, Robert Klemme wrote:
I was wondering whether there are any plans to include the plan of the
FK check in EXPLAIN output. Or is there a different way to get to see
all the plans of triggers as well as of the main SQL
558) AND (deleted IS
NULL))
Planning time: 1.613 ms
Execution time: 1392.732 ms
(14 rows)
Relevant indexes:
"table_name__enabled_date_end_enabled" btree (date_end, enabled)
"table_name__user_id" btree (user_id)
"table_name__user_id_deleted" btree (user_id, del
that's been toasted external, that's going to
produce it's own index scan of the toast table, which could then run
into conflicts with vacuuming.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? G
e to do it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pgsql-p
e, an
aggregate of each day would presumably work well; that would mean you'd
be reading 30 rows instead of 3.5M.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855
quire
stopping the process by attaching gdb and calling a function. I think
you also need a special compile.)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (8
of memory bandwidth,
especially since your access pattern is very scattered.
On 07/06/16 09:46, trafdev wrote:
Well, our CPU\RAM configs are almost same...
The difference is - you're fetching\grouping 8 times less rows than I:
Huh? The explain output certainly doesn't show that
ts. Next item, I'm without
a replica for 13+ hours, that's not good either.
Don't drop and add a node, just do a master switchover. AFAIK that's
nearly instant as long as things are in sync.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analyt
size?
What is the version of PostgreSQL you are using ?
You can execute the command "\l+" which will list all the databases and
their sizes.
Or you can execute "\l+ ".
Depending on your needs, you could also take the sum of
pg_class.relpages and multiply that by BLKSZ.
ervers into a state where the
only way to recover was to disconnect everyone.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list
rs?
Not sure how I can incorporate with my slon cluster, I guess that will
be the next thing I research.
Not sure I'm following, but you can pg_upgrade your replicas at the same
time as you do the master... or you can do them after the fact.
--
Jim Nasby, Data Architect, Blue Treble Con
isplay
a fairly low-res version of something like an xray, only pulling the raw
image if someone actually needs it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
thing) and restore it to another instance,
perhaps even something run from your /home.
Since pg_upgrade operates at a binary level, if you want to test it I'd
recommend using a PITR backup and not pg_dump. It's theoretically
possible to have a database that will pg_dump correctly but t
On 2/22/16 8:40 AM, Moreno Andreo wrote:
Il 18/02/2016 21:33, Jim Nasby ha scritto:
Depending on your needs, could could use synchronous replication as
part of that setup. You can even do that at a per-transaction level,
so maybe you use sync rep most of the time, and just turn it off when
difference.
Beyond that, there might be some advantage to putting blobs on their own
tablespace. Hard to say without trying it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreb
is the file io with lock holding. If io
spike happens, it would cause long query duration.
Am I correct? Look forward to any advice.
Thanks.
Regards,
Jinhua Luo
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Tro
432 <http://editor@127.0.0.1:6432> new
connection to server
This makes me think there's a race condition in pgBouncer, or that their
logging is just confusing.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Tr
tracked by
TRACE_POSTGRESQL_BUFFER_WRITE_DIRTY_START(), so I'd expect you to see it
in the relevant systemtap stats.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performan
roduce problems, but the regressions were only in the
sub 10% range in my test workload.
BTW, looks like Scott blogged about this along with some nice graphs:
https://sdwr98.wordpress.com/2016/01/07/transparent-huge-pages-or-why-dbas-are-great/
--
Jim Nasby, Data Architect, Blue Treble Consul
7;s locks until
the deadlock detector kicks in.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgres
al in the query itself for COLLATE "C"
to work?
I didn't realize the two methods were equivalent.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via
f you haven't already.
9.5 should release in January so you might want to wait for that version.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-p
, the sum of all column sizes, 3717MB ?
Probably per-page and per-tuple overhead.
What does SELECT reltuples, relpages FROM pg_class WHERE oid =
'public.alf_node_properties'::regclass show?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Ar
the tables on the slave?
Also, keep in mind that the first time you access rows on a Slony slave
after they're replicated Postgres will need to write hint bits out,
which will take some time. But that's clearly not the issue here.
--
Jim Nasby, Data Architect, Blue Treble Consulting,
post the output of EXPLAIN ANALYZE, preferably via
http://explain.depesz.com/
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list
eless to set it significantly less
than checkpoint_timeout in almost all cases. If you want ~5 minutes
between checkpoints checkpoint_timeout=30 seconds is way too low to be
useful. We should really change the default.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in
ync commit off
and leaving fsync alone in some cases. In other cases the difference
isn't enough to be worth it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
over optimistic about that. You could probably force the issue with an
ON SELECT ON table DO INSTEAD rule, but IIRC those aren't supported.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in T
couldn't, you'd probably
still see a performance gain from not firing up plpgsql on every row.
Though, if you didn't allow empty strings in last1, you could also just
replace that whole function with coalesce(). I see the function is
marked IMMUTABLE, which is good.
--
J
with amazon, especially if you can reproduce this.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@post
be not. Query times for data that has to come from the disk
can vary wildly based on what other activity is happening on the IO
system. Ultimately, your IO system can only do so many IOs Per Second.
[1]
https://wiki.postgresql.org/wiki/Index-only_scans#Index-only_scans_and_index-access_methods
-
On 10/27/15 3:56 PM, Bertrand Paquet wrote:
Tonight, the index on the three field is used, may be my yesterday
vacuum updated stats.
BTW, you can run just ANALYZE, which is *far* faster than a VACUUM on a
large table.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in
probing it very fast. I suspect that might be
faster than probing a regular index on the date field, but you should
test it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble
On 10/17/15 12:13 PM, Andres Freund wrote:
On 2015-10-17 10:26:01 -0500, Jim Nasby wrote:
Except inserts *do* take a lot of locks, just not user-level locks.
Operations like finding a page to insert into, seeing if that page is in
shared buffers, loading the page into shared buffers, modifying
it wouldn't surprise me if you're seeing radically
different behavior based on transaction duration.
Also, it sounds like perhaps longer transactions are involving more
tables? Is this a star schema you're dealing with?
--
Jim Nasby, Data Architect, Blue Treble Consulting, A
neither CPU or IO is maxed. IIRC there was nothing disturbing in
pg_locks either. I don't recall the server actually slowing down, but
this would have been on something with at least 24 cores, so...
My suspicion has always been that there's some form of locking that
isn't being
an all SSD setup.
It's also worth noting that there's some consensus that the optimizer is
generally too eager to switch from an index scan to a seqscan.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.co
erations down.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
than pg_restore tends to be.
Another possibility is filesystem snapshots, which could be even faster
than createdb --template.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing l
know about the exact ISO details, but your approach is the
correct one: find the date that the current week started on and then
build a range of [week start, week start + 7 days).
Also, note the use of [ vs ). That is the ONLY correct way to do this if
you're comparing to a timestamp.
--
59million records. What are all these ?
Each row can only be ~2KB wide, so any LO that's larger than that will
be split into multiple rows.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing
.) This is a NUMA related issue similar to the Mysql Swap Insanity issue:
Since you have that turned off, I don't think so.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-p
ransforms would be lossless, so
they could always be made, though we'd not want to do the transformation
if there was already an index on something like date_trunc(...).
I don't readily see any other data types where this would be useful, but
this is so common with timestamps tha
nsure that data is written to WAL (on disk)
BEFORE it is written to the data pages, you will probably have
corruption after a crash, and have no way to prevent or possibly even
detect the corruption.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble!
to the other machine
anyway. ISTM it'd be a LOT more useful to look at ways to make the WAL
logging of bulk inserts (and especially COPY into a known empty table) a
lot more efficient.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTrebl
On 4/13/15 4:13 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
Yeah, this is not something that would be very easy to accomplish, because a
buffer can get evicted and written to disk at any point. It wouldn't be too
hard to read every unlogged table during recovery and see if there are any
ity on the table that's probably
going to be completely useless.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscripti
probably increase the cost of some other stuff, so it's a tradeoff.
If this is that complex though you very likely would do better in
plperl, especially if you could pre-compile the RE's. AFAIK there's no
way to do that in Postgres, though it might be interesting to add that
ability
cularly. Nested loops are an example; if you miscalculate
either of the sides by very much you can end up with a real mess unless
the rowcounts were already pretty trivial to begin with.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
-
On 3/16/15 3:59 PM, Tomas Vondra wrote:
On 16.3.2015 20:43, Jim Nasby wrote:
On 3/13/15 7:12 PM, Tomas Vondra wrote:
(4) I suspect many of the relations referenced in the views are not
actually needed in the query, i.e. the join is performed but
then it's just discarded be
ve to wait in the pool for a free
connection for some time, but once they get one their work will get done.
I'm still waiting to find out how many CPUs on this DB server. Did i
miss it somewhere in the email thread below?
http://blog.musicbrainz.org/2015/03/15/postgres-troubles/ might sho
f you ultimately don't
refer to any of the columns in a particular table Postgres will remove
the table from the query completely.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-p
till a good idea with a VPS.
In the past I've used Linode, Digital Ocean, Vultr and RamNode. I've
become disheartened by Digital Ocean so don't want to use them for this
project.
You should take a look at
https://github.com/manageacloud/cloud-benchmark-postgres and
https://www
of an
un-filtered DELETE. It will both be much faster to perform and won't
leave any dead rows behind.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or
ISTM what we really want here is a time-based behavior, not number of
rows. Given that, could we do the index probe in a subtransaction, set
an alarm for X ms, and simply abort the subtransaction if the alarm fires?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Tre
not a
performance issue ?
Why are you dropping and re-loading? You mentioned it before and it
sounded like it had something to do with adding columns, but you don't
have to drop and reload to add a column.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Tr
of the same thing,
so if you materialize that one thing one time you can then use it to
speed up several views.
Using that approach means you'll have a lot less data that you have to read.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTre
ld be to create an
equivalent set of regular views in a different schema (which you can
probably do programmatically via pg_get_viewdef()) and then change the
search_path to put the new schema before the old.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it
ntion somewhere.
Is there one PG process that's clearly using more CPU than the others?
What else is running in the database? Are there any unusual data types
involved?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent v
On 2/2/15 9:37 PM, Slava Mudry wrote:
On Mon, Feb 2, 2015 at 5:52 PM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote:
On 2/2/15 7:36 PM, Jim Nasby wrote:
Currently the fact that it needs to go back to old tables
and FTS them
every 2B transa
On 2/2/15 7:36 PM, Jim Nasby wrote:
Currently the fact that it needs to go back to old tables and FTS them
every 2B transactions (or rely on autovacuum for this) and you can't do
anything about it (like permanently freeze the tables) seems like a big
scalability issue. Does i
On 2/1/15 3:08 PM, Josh Berkus wrote:
On 01/30/2015 05:34 PM, Jim Nasby wrote:
On 1/30/15 2:26 PM, Josh Berkus wrote:
This would probably work because there aren't a lot of data structures
where people would have the same key:value pair in different locations
in the JSON, and care abo
iable method for changing
relfrozenxid as soon as someone inserts a new tuple in the relation. It
might be possible to tie this into the visibility map, but no one has
looked at this yet.
Perhaps you'd be willing to investigate this, or sponsor the work?
Thank you.
On Fri, Jan
performed by opening a cursor :
I don't think the cursor is the issue here, but can you try running
those same commands directly on the remote server to make sure?
It looks like it's the fetch itself that's slow, which makes me wonder
if there's some network or other problem.
bad. Have you tried reindexing? That might help. You could
also try something like trigram
(http://www.postgresql.org/docs/9.1/static/pgtrgm.html); it might be faster.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-perf
inary comparison. text will worry about conversion and what not
(though, perhaps there's a way to force that to use C or SQL instead of
something like UTF8, short of changing the encoding of the whole database).
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it
N). Obviously doesn't help for stuff you haven't indexed, but
presumably if you care about performance and have any significant size
of data you've also indexed parts of the JSON, yes?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTre
t aggregates that data into fewer transactions for the main
cluster.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
useful, though for different reasons.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
.5TB ram, 32 cores and all
SSD storage) but probably still good for this test.
Awesome! If there's possibility of developers getting direct access, I suspect
folks on -hackers would be interested. If not but you're willing to run tests
for folks, they'd still be interested. :)
ing_record
USING gin ( status gin_trgm_ops) wherestatus NOT IN ('ANULLED') and
gender='MALE' ;
explain (analyse on,buffers on) select T.form_id from
TAR_MVW_targeting_record AS T where T.status NOT IN ('ANULLED') AND
T.household_member_last_name ili
t to the first live tuple. (This was run close enough to the vacuum that I
don't think visibility of these tuples would have changed mid-stream).
--
Jim Nasby, Lead Data Architect (512) 569-9461
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to
1 - 100 of 196 matches
Mail list logo
