s - whereas when you specify it
> > using a size unit (like MB, GB,...) that amount of memory is divided by
> > the size of a page. So you're off by a factor of 8192.
> >
> > Greetings,
> >
> > Andres Freund
> >
> > --
> > Andres Freund http://www.2ndQuadrant.com/
> > PostgreSQL Development, 24x7 Support, Training & Services
> >
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
tore a bunch of it, then check the resultant
size of the tables on disk vs. the actual size of the data. That's
really the only way to know since the actual efficiency of data
storage depends a lot on the data itself.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@
ng to execute the query, and the query
itself.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
y:
INSERT INTO check_date VALUES (null, 1, null);
or even:
INSERT INTO check_date
VALUES (to_date(null, 'mmddhh24miss'), 1, to_date(null,
'mmddhh24miss'));
both of which result in what you desire.
null and the empty string are not the same thing.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
icate using Slony instead of streaming, which allows
you to create additional tables on the replica that are read/write in
addition to triggers that only fire on the replica. It's complicated, but
pretty damn powerful.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
and see if the rows are sill nonremovable. I bet you $5.34 that
everything works fine after that, which would indicate that the folks
who made the snapshot didn't do it correctly.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ntacting $random_legal_service.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
he situation on
the list. That's why I'm just looking for a lawyer who understands
the situation and can advise me.
>
> On Wed, Mar 11, 2015 at 1:28 PM, Bill Moran
> wrote:
>
> >
> > I've been asked to sign a legal document related to a PostgreSQL-
>
a tuples as the data in the table changes. It's not
unusal for the table to be 2x the size of the actual data on
a heavily updated table.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
8
>
> But if I do:
>
> DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02'
>
> It DOES work.
>
> Why the db doesn't recognize the name of the table without quotes?.
See:
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
) was terminated by
> signal 9: Killed
> 2015-04-01 06:24:38 EDT DETAIL: Failed process was running: analyze verbose;
> 2015-04-01 06:24:38 EDT LOG: terminating any other active server processes
>
> I started this process at 11PM, so it ran for about 7.5 hours before
> crashing.
On Wed, 1 Apr 2015 06:26:36 -0700 (MST)
TonyS wrote:
> On Wed, April 1, 2015 8:48 am, Bill Moran [via PostgreSQL] wrote:
> >
>
> >>>> Running "analyze verbose;" and watching top, the system starts out
> >>>> using no swap data and about 4GB
r your memory problems. I'd suggest to set it
> > to 16MB, and see if you can avoid "on disk" sorting. If not - gradually
> > increase work_mem.
> >
> > Regards,
> >
> >
> > Igor Neyman
> >
>
>
> Thanks Igor,
>
> I will tr
r of tries). Is this
> possible without going into pgsql source code?
I suspect that savepoints will accomplish what you want:
http://www.postgresql.org/docs/9.4/static/sql-savepoint.html
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
you can not
guarantee that, and it doesn't hold true for all functions.
In general, it's inappropriate for a function to be able to manipulate
a transaction beyond aborting it. And the abort has to bubble up so
that other statements involved in the transaction are also notified.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ror and the
data won't be accepted. The same thing happens if you try to
store invalid XML in an XML field (such as XML without proper
closing tags, etc). It seems that this strictness causes a lot
of people to avoid those data types, as there seem to be a lot
of people who would rather have garb
irely possible I missed a memo, so I am
> > open to a more detailed explanation of the inefficiencies involved.
> >
>
> The Postgres source is written in C, not in plpgsql. C has a good
> optimizing compiler and plpgsql doesn't.
Maybe that's a roundabout way of
ell enough. There are certainly cases when you want to create very complex
logic in the database and plpgsql is liable to make that difficult. But
there are a lot of cases where having to manage pointers and a build
environment and all the things that go with C aren't justified, because
plpgsql
tand how the
connection pool
works.
You could confirm this by turning on full query logging in Postgres and see
which connection
does what. If it turns out to be the case, then you'll have sort out how your
code is
getting confused.
If it's not the case, then I don't have any oth
x27;t find
any information on why that sit is down or where it might have
gone to.
Is this a temporary outage? Or has the RPM data moved somewhere
else and isn't documented yet? Any help is appreciated.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.or
I'm working on a project converting a bunch of code from another database
system to PostgreSQL.
One of the issues is that the previous system accepted integers and binary
data in the same hex format as C ... i.e. 0xff
I understand that the proper way to handle this in postgres is x'ff', but
the
On Thu, 21 May 2015 13:57:24 -0400
Tom Lane wrote:
> Bill Moran writes:
> > My other question: is there a specific reason why PostgreSQL doesn't support
> > this syntax, aside from "nobody has bothered to add such support"? Because
> > I'm considering w
On Fri, 22 May 2015 11:02:47 -0400
Tom Lane wrote:
> Alban Hertroys writes:
> > On 22 May 2015 at 04:46, Bill Moran wrote:
> >> With all that being said, if I were to build a patch, would it be likely
> >> to be accepted into core?
>
> > Wouldn't yo
On Fri, 22 May 2015 11:27:49 -0500
Dennis Jenkins wrote:
> On Fri, May 22, 2015 at 10:02 AM, Tom Lane wrote:
>
> > Alban Hertroys writes:
> > > On 22 May 2015 at 04:46, Bill Moran wrote:
> > >> With all that being said, if I were to build a patch, would i
On Fri, 22 May 2015 12:44:40 -0400
Tom Lane wrote:
> Bill Moran writes:
> > Tom Lane wrote:
> >> Other questions you'd have to think about: what is the data type of
> >> 0x; what do you do with 0x (too big
> >> even f
s=51340 width=66)"
> "Index Cond: (group_id = subset.id)"
> Total query runtime: 3986 ms. 5978 rows retrieved.
>
>
> select * from newtable where group_id IN (select * from subset)
> "Hash Join (cost=41.25..138092255.85 rows=1935067087 width=66)"
> " Hash Cond: (newtable.group_id = subset.id)"
> " -> Append (cost=0.00..84877869.72 rows=3870134173 width=66)"
> "-> Seq Scan on newtable (cost=0.00..0.00 rows=1 width=66)"
> "-> Seq Scan on newtable_01 (cost=0.00..946235.96 rows=46526896
> width=66)"
> ...
> "-> Seq Scan on newtable_86 (cost=0.00..986527.64 rows=44269664
> width=66)"
> " -> Hash (cost=38.75..38.75 rows=200 width=8)"
> "-> HashAggregate (cost=36.75..38.75 rows=200 width=8)"
> " -> Seq Scan on subset (cost=0.00..31.40 rows=2140 width=8)"
> Execution Cancelled after 766702 ms !
>
> I tried the same with "SET enable_seqscan = OFF" and got an index scan of all
> tables;
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
62 ms.
> 1 row retrieved.
>
> This time, the history tab shows that both took the same time to run (an
> improvement!?)
If your environment is providing such wildly variant results, then
you need to start running multiple tests instead of assuming that a single
run of a query is indicative of a pattern.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ms
> > Execution time: 1.390 ms
> > (15 rows)
> >
> > regards, tom lane
>
> Wow, sorry I screwed up the query. It should be:
>
> ORDER BY c.created_at DESC
>
> Not b, or as you noted its trivial to index. Sorry!
Creating an index on c.created_at sped things up by a factor of over
1000, which caused the case you defined to run in ~0.5ms for me.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> but follows a power law (some user_id would return millions of records while
> others only one).
> This is the farthest I can go at this point. Maybe someone can provide me
> with more explanations regarding planner's behavior and ways to go further
> to make it work properl
Db is a failover manager which relies on virtual IP
> management, not the one I described above.
pgpool has this capacity.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
b, gis_iti_itinerario iti_sub
> where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem
>and via_sub.via_status_viagem = 'A'
>and via_sub.via_dt_hora_ini > now() - interval '9 hours'
>and iti_sub.lin_cod_linha = 389
>and iti_sub.iti
hould I increase the statistic target to 500, or even
> to 1000?
> Is there something else I can trigger to get the appropriate plan?
>
> Comments/explanations would be appreciated
> Daniel
>
>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [m
Please do not remove the mailing list from replies. See below.
On Fri, 12 Jun 2015 09:21:19 -0300
Anderson Valadares wrote:
> 2015-06-08 20:33 GMT-03:00 Bill Moran :
>
> > On Mon, 8 Jun 2015 11:59:31 -0300
> > Anderson Valadares wrote:
> >
> > > Hi
> >
On Mon, 15 Jun 2015 17:48:54 -0300
Anderson Valadares wrote:
> 2015-06-12 19:56 GMT-03:00 Bill Moran :
>
> > Please do not remove the mailing list from replies. See below.
> >
> > On Fri, 12 Jun 2015 09:21:19 -0300
> > Anderson Valadares wrote:
> >
> &g
ase function that can be used
to compress data; you'd have to write your own or do it at the application
level.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
de: Canceled on identification as a pivot, during
> commit attempt.
> HINT: The transaction might succeed if retried.
>
> and
>
> ERROR: could not serialize access due to read/write dependencies among
> transactions
> DETAIL: Reason code: Canceled on commit attempt with conflict in from
> prepared pivot.
> HINT: The transaction might succeed if retried.
>
> Thanks!
--
Bill Moran
--
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, 17 Jun 2015 10:33:37 -0300
Anderson Valadares wrote:
> 2015-06-15 18:19 GMT-03:00 Bill Moran :
>
> > On Mon, 15 Jun 2015 17:48:54 -0300
> > Anderson Valadares wrote:
> >
> > > 2015-06-12 19:56 GMT-03:00 Bill Moran :
> > >
> > > > P
uld emit a warning when you used the non-standard way
> of escaping single quotes (unless you explicitly turned that off)
>
>
> Could you please provide below information.
> How to change standard_conforming_strings value of postgresql.conf? I have
> checked but this option is
be a BEFORE trigger FOR EACH ROW. Otherwise, the returned
value
won't do anything. It should read like this:
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_ebscb_spa_log02
FOR EACH ROW EXECUTE PROCEDURE on_ai_myTable();
If you created it with AFTER INSERT or FOR EACH STATEMENT, then the trigger
won't
work as desired.
The other thing about assignment being := was already mentioned.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2768 Batches: 2 Memory Usage: 159kB
> -> Seq Scan on table84 table84 (cost=0.00..14600.96
> rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1)
> Total runtime: 13458.301 ms
> (12 rows)
>
> Thank you again for your advice and I hope that with your help I'll be able
> to solve this issue.
>
> Best regards.
> Lukasz
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
dd capacity at
minimal cost.
There's nothing like a VM where you never know what the performance
will be because you never know when some other VMs (completely unrelated
to you and/or your work) will saturate the IO with some ridiculous
grep recursive command or something.
--
Bill Moran
ng that if your database has a lot of indexes, the pg_dump
might actually be faster.
But the only way to know is to try it out on your particular system.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
far as when things get evicted from memory, you'll have to look at the
source code, but it's your typical "keep the most commonly needed data in
memory" algorithms.
What problem are you seeing? What is your performance requirement, and what
is the observed performance? I as
.
> However, I know from experience that's not entirely true, (although it's not
> always easy to measure all aspects of your I/O bandwith).
>
> Am I missing something?
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
gt; and we'll see where it goes ...
> >
> > regards, tom lane
>
>
>
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
sort by oid then in the
> oid group sort by relname?
> Can somebody explain what does the database done for hashAggregate?
It combines the values for oid and relname for each returned row, generates a
hashkey
for them, then uses that hashkey to aggregate (compute the GROUP BY,
essentially, in
th
he best way to synchronise JUST the changes on a table
> between servers please?
Sounds like a problem custom-made to be solved by Slony:
http://slony.info/
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
the
session data in a Postgres table with great success.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ackers list
so the developers can chime in. My opinion is that this is a bug, but it's
an obscure enough bug that it's not surprising that it's gone unfixed for
a while.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
things like user's groups, and it does a pretty good
job of letting us coordinate activities. Basic membership on the
site is free and includes participating in as many groups as you
desire. (it only costs something if you want to host your own group).
Hope to see you soon.
--
Bill Moran
of the server, or have some command
that tests to ensure the server is started and blocks until it is
before running the create command.
The only point I'm unclear on is whether you've confirmed that
Postgres actually _is_ started once the server is up (albiet without
the CREATE statem
the
problem occurs, and the contents of the pg_locks table when
the problem is occurring.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
les every 5 minutes (based on the config you show) ... so are
you doing the inserts then checking the table without leaving enough time
in between for the system to wake up and notice the change?
--
Bill Moran
--
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, 4 Nov 2015 16:43:57 +0100
Bertrand Roos wrote:
>
> Le 04/11/2015 14:55, Bill Moran a écrit :
> > On Wed, 4 Nov 2015 14:32:37 +0100
> > Bertrand Roos wrote:
> >> I try to configure auto-analyse task with postgresql 9.4.
> >> I have the following
gt; return false if count is less it returns true.
An exclusion constraint might be a better solution.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
gets
interrupted. I'm trying to understand if the partial runs are at least
making _some_ progress so the next vacuum has less to do, or if this is
a serious problem that I need to fiddle with tuning to fix.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To ma
just noticed it on a
particularly problematic day last time I looked.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
t be a bug in Postgres, does anyone have
any suggestions on what other ways the stats could be reset that I need to check
on? Has anyone else experienced this to lend credence to the possibility that
it's
a bug? I have no clue how to reproduce it, as the occurrance is rare and still
seems rando
any more.
Anyone know? Or, alternatively, anyone have another option to get the
same job done?
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
rest of the data on the other
database server configured with higher shared_buffers.
I know these probably aren't the kind of answers you're looking
for, but I don't have anything better to suggest; and the rest
of the mailing list seems to be devoid of ideas as well.
--
Bill Mo
E EXTERNAL;
?
The default storage for a JSONB field is EXTENDED. Switching it to
EXTERNAL will disable compression. You'll have to insert your data over
again, since this change doesn't alter any existing data, but see
if that change improves performance.
--
Bill Moran
--
Sent via pgsq
knowledge) they're the best answers available at this time. I'd really
like to build the alternate TOAST storage, but I'm not in a position to
start on a project that ambitious right ... I'm not even really keeping
up with the project I'm currently supposed to be doing.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If it's
happening frequently, you'll want to investigate what process is holding
the locks for so long and see what can be done about it.
--
Bill Moran
--
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, 2 Dec 2015 09:31:44 -0800
Christophe Pettus wrote:
>
> On Dec 2, 2015, at 9:25 AM, Bill Moran wrote:
>
> > No. See the section on row level locks here:
> > http://www.postgresql.org/docs/9.4/static/explicit-locking.html
>
> That wasn't quite my ques
The third solution is probably _really_ the correct one, from
a pedantic standpoint, but it's a bit more work to implement.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
postgres [local] SELECT
> > -- idle again, memory still allocated
> > 26851 postgres 20 0 2365732 920668 918748 S 0.0 22.7 1:22.54
> > postgres: postgres postgres [local] idle
> >
> > Memory will only be released if psql is exited. According to the
> > PostgreSQL design memory should be freed when the transaction completed.
> >
> > top commands on FreeBSD: top -SaPz -o res -s 1
> > top commands on Linux: top -o RES d1
> >
> > Config: VMs with 4GB of RAM, 2 vCPUs
> > shared_buffers = 2048MB # min 128kB
> > effective_cache_size = 2GB
> > work_mem = 892MB
> > wal_buffers = 8MB
> > checkpoint_segments = 16
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, 13 Dec 2015 16:35:08 +0100
Gerhard Wiesinger wrote:
> Hello Bill,
>
> Thank you for your response, comments inline:
>
> On 13.12.2015 16:05, Bill Moran wrote:
> > On Sun, 13 Dec 2015 09:57:21 +0100
> > Gerhard Wiesinger wrote:
> >> some further de
fically memory-inefficient
manner. Since you mention that you see nothing in the PG logs, that makes it
even more likely (to me) that you're looking entirely in the wrong place.
I'd be willing to bet a steak dinner that if you put the web server on a
different server than the DB, that the memory problems would follow the
web server and not the DB server.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, 13 Dec 2015 22:23:19 +0100
Gerhard Wiesinger wrote:
> On 13.12.2015 21:14, Bill Moran wrote:
> > Wait ... this is a combined HTTP/Postgres server? You didn't mention that
> > earlier, and it's kind of important.
> >
> > What evidence do you have
sync, which is the only thing diskchecker.pl tests for.
> >
>
> I was thinking that since the disk have a 32M write-cache (with not
> battery) it would lie to the OS (and postgres) about when data are really
> on disk (not in the disk write cache). But maybe that thinking was wrong
seriously doubt that trying to make your UUIDs generate in a
predictable fashon will produce any measurable improvement, and I
see no evidence in the articles you cited that claims otherwise
have any real basis or were made by anyone knowledgeable enough
to know.
--
Bill Moran
--
Sent via p
go, you may want to try
something more along the lines of this for your check:
SELECT true WHERE NOT EXISTS(SELECT 1 FROM keyz WHERE xfk NOT IN (akeys($1)));
Not tested, so it's possible that I have some typo or something; but overall
I've found that the NOT EXISTS construct
t
will always involve _some_ pain, but less is better.
I've done the job of #3 with other groups, and 99% of the time
there was nothing to do. The one incident I had to handle was
terrible, but at least I had some guidance on how to deal with
it.
--
Bill Moran
--
Sent vi
ritical remarks regarding patches and/or technical work are
necessary to ensure a quality product; however, critical remarks
directed at individuals are not constructive and therefore not
acceptable." or something ...
--
Bill Moran
--
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, 19 Jan 2016 23:53:19 -0300
Alvaro Herrera wrote:
> Bill Moran wrote:
>
> > As far as a current solution: my solution would be to decompose the
> > JSON into an optimized table. I.e.:
> >
> > CREATE TABLE store1 (
> > id SERIAL PRIMARY KEY,
> >
bles. How often is something that will require some guesswork
and/or experimenting, but I would recommend at least once per hour. Since
you're only vacuuming selected tables, the performance impact should be
minimal.
You'll have to do a VACUUM FULL on the bloated tables _once_ to get the siz
On Thu, 28 Jan 2016 00:37:54 +0100
Ivan Voras wrote:
> On 28 January 2016 at 00:13, Bill Moran wrote:
>
> > On Wed, 27 Jan 2016 23:54:37 +0100
> > Ivan Voras wrote:
> >
> > > So, question #1: WTF? How could this happen, on a regularly vacuumed
> > >
n of the schema, as a primary key implies that other tables can
rely on this set of columns as a unique identifier for rows. This
metadata may be used by external programs, but is also utilized interally
by the server in some cases."
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
to figure out _which_ rows
to fetch faster on the small table than the large table, which is what
you would expect, since a smaller index should be faster than a large one.
However, when it goes to actually fetch the row data, it takes
significantly longer on the small table, despite the fact that it's
only fetching 1/3 as many rows. It is, however, doing 2.5x as many
disk reads to get those rows: For the large table, it reads 61MB from
disk, but it reads 160MB to get all the data for the smaller table.
How the data was inserted into each table could lead to similar data
being clustered on common pages on the large table, while it's spread
across many more pages on the small table.
That still doesn't explain it all, though. 2.5x the disk
activity normally wouldn't equate to 28x the time required. Unless
you're disks are horrifically slow? Does this server have a lot of
other activity against the disks? I.e. are other people running
queries that you would have to contend with, or is the server a VM
sharing storage with other VMs, or even a combined use server that
has to share disk access with (for example) a web or mail server
as well? Is the performance difference consistently ~28x?
Other things: what is shared_buffers set to? The queries would seem
to indicate that this server has less than 1M of those two tables
cached in memory at the time you ran those queries, which seems to
suggest that either you've got shared_buffers set very low, or that
there are a lot of other tables that other queries are accessing at
the time you're running these. Perhaps installing pg_buffercache to
have a look at what's using your shared_buffers would be helpful.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
u the former, whereas
CITEXT will give you both.
I don't think your syntax will work, though. I'm guessing that
PRIMARY KEY pk_stock_code lower(stock_code) will, though.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your su
hat writes completed successfully, otherwise, Postgres would
be able to recover after a restart.
Beyond that, running Postgres on a filesystem that frequently fills up
is going to be problematic all around anyway. If you don't improve the
hardware situation, you're going to continue to have problems like this.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
o you have any idea what other systems to try?
http://dbsteward.org/
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
TO mro;
>
> It complicates the usage of pg_dump to compare the structures of the two
> similar databases like DEV and PROD, two development branches etc.
I don't think pg_dump was ever intended to serve that purpose.
dbsteward, on the other hand, does what you want:
https://gith
f they differ if you cannot recreate the correct one
> > exactly from source-controllled DDL? Or know how they are supposed to
> > differ if this is a migration point?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
um_md5", "st_size")
> WHERE "checksum_md5" IS NOT NULL
>
> *Question:*
>
> What else can I do to improve the Performance of the Query?
>From the explain, it looks like the biggest pain point is the
inode_segments table, specifically, this condition:
s.full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
It's doing a full scan of every record in that table, which is
a large number, and that regex can't be cheap over that kind of
volume.
If you do:
SELECT count(*)
FROM inode_segments
WHERE full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+';
how many tuples actually match that condition? If the number
is a large percentage of the total table, then I'm not sure
how to help you, but if the percentage is small, you might
be able to speed things up by adding an index:
CREATE INDEX is_fp_trunc_idx ON inode_segments(substring(full_path FROM 1 FOR
19));
Then adding this condition to the where clause:
substring(s.full_path FROM 1 FOR 19) = '/userfiles/account/'
There are other index combinations that may help as well,
depending on the nature of the values in that table, but,
in general, anything you can do to reduce the number of
records that have to be examined in that table is liable
to speed things up.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
eneral theme, a lot of developers don't
seem to think it's useful for them to know SQL, and therefore don't
bother trying -- or even actively resist learning.
So if the overall theme is "knowing this makes things better", I would
buy multiple copies of the book an mysteriousl
no
matter what happens during.
As an aside, you can only fit so many gallons into a 10 gallon
container. You might simply have to accept that your requirements
now exceed the capacity of the RR connection and upgrade.
--
Bill Moran
http://www.potentialtech.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ome way by this audience.
See the docs. postgresql.conf has options to log every SQL statement,
or to log only the SQL statements that exceed a certain time limit.
Unless I'm misunderstanding your question?
--
Bill Moran
http://www.potentialtech.com
---(end of broad
You can tweak pg_hba.conf to disallow all access to that particular
database. Will that accomplish what you want?
--
Bill Moran
http://www.potentialtech.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
he id of the latest
> row, which is several inserts after mine?
Don't do that. Please let us know what site recommended that so I can
send an email to the author correcting them.
Instead, do SELECT currval(''), which is guaranteed to be isolated
from other sessio
There are parameters for the specific things you want to control:
http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html
HTH
--
Bill Moran
http://www.potentialtech.com
---(end of broadcast)---
TIP 1: if posting/reading through Usen
Steve Lefevre <[EMAIL PROTECTED]> wrote:
>
> Bill Moran wrote:
> > Don't do that. Please let us know what site recommended that so I can
> > send an email to the author correcting them.
> >
> Hello Bill -
>
> The 'offending' site and artic
ously deleting the data. If that's the case, you should be able to
track it down next time it happens if you have statement logging enabled.
--
Bill Moran
http://www.potentialtech.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
to tell you how much you can handle.
Since you don't describe anything about the schema or application, I can't
say for sure, but over the last six months, every time this has come
up, we've been able to fix the problem by reorganizing the data some
(i.e. materialized data, temp tables
n this runs from two different places,
the DB may order the returned values in a different order for each one,
which leads to the possibility of two similar inserts deadlocking. Unless
I misunderstand your schema, you should be able to guarantee against
deadlocking by guaranteeing that the SELEC
x27;re a troll, go away.
--
Bill Moran
http://www.potentialtech.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
t();
>
> This all works ok, but if I do
>
> alter table test drop column tekst;
>
> then select * from sel_test(); doesn't work anymore
> Even if I recreate the function.
>
> Is this a known problem?
Have you tried altering the table, then disconnect and reconn
r doing other work.
There are also a few processes that are always running depending on your
configuration: the background writer, and possibly the autovacuum process.
These will use CPU if they're actually doing work.
--
Bill Moran
http://www.potentialtech.com
--
here _will_ be _some_ wasted space, but (in my experience, at least) this
will hit a plateau and level off. Frequent VACUUM FULLs bloat your indexes
and require frequent REINDEXES, so should be avoided unless needed.
--
Bill Moran
http://www.potentialtech.com
---(end of br
n_US.UTF-8' # locale for time formatting
> --- --- --- ---
> I explain the characterization of fast and slow like this: Slow is taking
> about ten times longer than fast to execute the same query.
>
> If there's any gotcha here that we're not seeing, please point it out. I'm
> flummoxed.
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/
--
Bill Moran
http://www.potentialtech.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
1 - 100 of 829 matches
Mail list logo
