On Mon, Nov 27, 2017 at 10:40 AM, Scott Marlowe
wrote:
>
> Generally VMs are never going to be as fast as running on bare metal
> etc. You can adjust it and test it with something simple like pgbench
> with various settings for -c (concurrency) and see where it peaks etc
On Nov 21, 2017 00:05, "Matthew Hall" wrote:
> Are all indexes present at the time you insert? It will probably be much
faster to insert without the gin index (at least) and build it after the
load.
There is some flexibility on the initial load, but the updates in the
On Sat, Dec 2, 2017 at 8:04 PM, Justin Pryzby <pry...@telsasoft.com> wrote:
> On Sat, Dec 02, 2017 at 05:27:51PM -0800, Jeff Janes wrote:
> > I think the non-extended stats code also has trouble with booleans.
> > pg_stats gives me a correlation of 0.8 or higher for the
On Dec 3, 2017 15:31, "Tom Lane" <t...@sss.pgh.pa.us> wrote:
Jeff Janes <jeff.ja...@gmail.com> writes:
> On Sat, Dec 2, 2017 at 8:04 PM, Justin Pryzby <pry...@telsasoft.com>
wrote:
>> It thinks there's somewhat-high correlation since it gets a list of
On Fri, Dec 1, 2017 at 11:08 PM, Vitaliy Garnashevich <
vgarnashev...@gmail.com> wrote:
>
>
> seq_page_cost = 0.0
> random_page_cost = 0.0
> explain analyze select * from aaa where num = 2 and flag = true;
>
> Bitmap Heap Scan on aaa (cost=753.00..2003.00 rows=10257 width=5) (actual
>
On Thu, May 31, 2018 at 7:22 AM, Nicolas Seinlet
wrote:
> Hi,
>
> I have a query with a strange query plan.
>
> This query is roughly searching for sales, and convert them with a
> currency rate. As currency rate changes from time to time, table contains
> the currency, the company, the rate,
On Sun, Dec 24, 2017 at 11:51 AM, Jean Baro wrote:
> Hi there,
>
> We are testing a new application to try to find performance issues.
>
> AWS RDS m4.large 500GB storage (SSD)
>
Is that general purpose SSD, or provisioned IOPS SSD? If provisioned, what
is the level of
On Tue, Jan 2, 2018 at 6:02 AM, Rick Otten wrote:
> After reading this article about keys in relational databases, highlighted
> on hacker news this morning:
> https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html
>
> I keep pondering the performance chart,
On Wed, Jan 10, 2018 at 3:59 AM, Nandakumar M wrote:
>
> I am not using prepared statements. Postgres documentation and previous
> questions in the pgsql-performance mailing list mention that the query plan
> is cached only when prepared statements are used.
>
>
On Fri, Jan 12, 2018 at 12:03 AM, Nandakumar M wrote:
> Hello Jeff,
>
> Thanks for the insights.
>
> >Don't keep closing and reopening connections.
>
> Even if I close a connection and open a new one and execute the same
> query, the planning time is considerably less than
On Wed, Jan 31, 2018 at 4:03 AM, Vitaliy Garnashevich <
vgarnashev...@gmail.com> wrote:
>
> The results look really confusing to me in two ways. The first one is that
> I've seen recommendations to set effective_io_concurrency=256 (or more) on
> EBS.
I would not expect this to make much of a
On Mon, Jul 30, 2018 at 3:02 PM, Andres Freund wrote:
> Hi,
>
> On 2018-07-30 13:31:33 -0400, Jeff Janes wrote:
> > I don't know where the time is going with the as-committed JIT. None of
> > the JIT-specific timings reported by EXPLAIN (ANALYZE) add up to anything
>
On Mon, Jul 30, 2018 at 12:01 PM, Pavel Stehule
wrote:
>
>
> 2018-07-30 13:19 GMT+02:00 Jeff Janes :
>
>> On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule
>> wrote:
>>
>>> 2018-07-30 1:00 GMT+02:00 Tom Lane :
>>>
>>>> David Rowley
On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule
wrote:
> 2018-07-30 1:00 GMT+02:00 Tom Lane :
>
>> David Rowley writes:
>> > On 29 July 2018 at 17:38, Dinesh Kumar wrote:
>> >> I found performance variance between accessing int1 and int200 column
>> which
>> >> is quite large.
>>
>> > Have a
On Mon, Jul 30, 2018 at 1:23 PM, Andres Freund wrote:
> On 2018-07-30 07:19:07 -0400, Jeff Janes wrote:
>
> > And indeed, in my hands JIT makes it almost 3 times worse.
>
> Not in my measurement. Your example won't use JIT at all, because it's
> below the cost threshold.
On Tue, Aug 14, 2018 at 9:18 AM, Alexis Lê-Quôc wrote:
>
each
running PG 9.3
on linux
That is the oldest version which is still supported. There have been a lot
of improvements since then, including to performance. You should see if an
upgrade solves the problem. If not, at least you will
On Tue, Jul 10, 2018 at 11:07 AM, Lincoln Swaine-Moore <
lswainemo...@gmail.com> wrote:
>
>
>
> Something about the estimated row counts (this problem persisted after I
> tried ANALYZEing)
>
What is your default_statistics_target? What can you tell us about the
distribution of parent_id?
On Tue, Jul 17, 2018 at 1:00 AM, Neto pr wrote:
> Dear,
> Some of you can help me understand this.
>
> This query plan is executed in the query below (query 9 of TPC-H
> Benchmark, with scale 40, database with approximately 40 gb).
>
> The experiment consisted of running the query on a HDD (Raid
On Mon, Jul 16, 2018 at 5:29 PM, Lincoln Swaine-Moore <
lswainemo...@gmail.com> wrote:
> Tom and Jeff,
>
> Thanks very much for the suggestions!
>
> Here's what I've found so far after playing around for a few more days:
>
> What is your default_statistics_target? What can you tell us about the
On Wed, Sep 5, 2018 at 12:21 AM Dinesh Kumar wrote:
> Hi All,
> I was wondering whether the case is solved or still continuing. As a
> Postgres newbie, I can't understand any of the terms (JIT, tuple
> deformation) as you mentioned above. Please anyone let me know , what is
> the current
On Fri, Sep 7, 2018 at 8:00 AM Patrick Molgaard wrote:
> Hi folks,
>
> I've been seeing some curious behaviour on a postgres server I administer.
>
> Intermittently (one or two times a week), all queries on that host are
> simultaneously blocked for extended periods (10s of seconds).
>
> The
On Fri, Sep 7, 2018 at 2:03 PM Patrick Molgaard wrote:
>
> Hi Jeff,
>
> Thanks for your reply. Are locks relevant in this case, though?
>
I don't know, but why theorize when we can know for sure? It at least
invokes VirtualXactLockTableInsert. I don't see how that could block on a
heavyweight
On Sun, Mar 11, 2018 at 10:33 AM, dangal wrote:
> jeff thank you very much for your time, I tell you, they are the same
> queries
> with the same parameters, I take 3 minutes for example, but I execute it
> and
> it takes me seconds, that's why I suspect it is the
On Fri, Oct 19, 2018 at 3:19 AM Yavuz Selim Sertoglu <
yavuzselim.serto...@medyasoft.com.tr> wrote:
> Hi all,
>
> I have a problem with my query. Query always using parallel bitmap heap
> scan. I've created an index with all where conditions and id but query does
> not this index and continue to
On Mon, Oct 22, 2018 at 3:20 AM Yavuz Selim Sertoglu <
yavuzselim.serto...@medyasoft.com.tr> wrote:
> Thanks for the reply Jeff,
>
> I know 20ms is nothing but it shows me that there is a problem with my
> configuration. I want to find it.
>
This is a dangerous assumption. This is no
On Tue, Oct 30, 2018 at 3:50 PM Ulf Lohbrügge
wrote:
> When I use the psql cli on the same database I can see via "\timing" that
> the first statement after "RESET ROLE;" is significantly slower. I was even
> able to strip it down to two statements ("SET ROLE ...;" and "RESET ROLE;"):
>
> ...
>
>
> 4)delete in chunks :
> do $$
> declare
> rec integer;
> begin
> select count(*) from my_table into rec where end_date <=
> to_date('12/12/2018','DD/MM/') and end_date >
> to_date('11/12/2018','DD/MM/');
> while rec > 0 loop
> DELETE FROM my_Table WHERE id IN (select id from
On Sat, Dec 29, 2018 at 7:17 AM Jim Finnerty wrote:
> Jeff, can you describe the changes that were made to ANALYZE in v11,
> please?
>
> I've found that running ANALYZE on v10 on the Join Order Benchmark, using
> the default statistics target of 100, produces quite unstable results, so
> I'd be
On Sat, Dec 29, 2018 at 1:58 AM David Rowley
wrote:
> On Sat, 29 Dec 2018 at 04:32, Justin Pryzby wrote:
> > I think the solution is to upgrade (at least) to PG10 and CREATE
> STATISTICS
> > (dependencies).
>
> Unfortunately, I don't think that'll help this situation. Extended
> statistics are
On Tue, Nov 27, 2018 at 9:17 AM Sanyo Moura wrote:
> Hi,
>
> I'm running performance tests for my application at version 11.1 and
> encountered
> queries with high planning time compared to the same planning, running at
> versions 10.5 and 11.0.
>
Can you reproduce the regression if the tables
On Mon, Nov 26, 2018 at 5:11 AM Viswanath wrote:
> *Postgres server version - 9.5.10*
> *RAM - 128 GB*
> *WorkMem 64 MB*
>
> *Problematic query with explain :*
> *Query 1 (original):*
> explain analyse SELECT myTable1.ID FROM myTable1 LEFT JOIN myTable2 ON
> myTable1.ID=myTable2.ID WHERE
>
>
> *Performance issue:*
>
> I’m trying to figure out if PostgreSQL (PG) has some inherent limit on
> IOPS per connection.
>
> Running pgbench with multiple clients (-c 30) we are able to see 20K+ IOPS
> , which is what we expect. But, if we use just one client, we get 1200
> IOPS, avg disk
On Wed, Jan 9, 2019 at 3:35 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
>
> Now, In machine 1 when I run psql I get the prompt password but in machine
> 2 I keep getting the next error :
>
> psql: could not connect to server: No such file or directory
> Is the server
On Wed, Jan 9, 2019 at 10:09 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> Hey Tom,
> I'm aware of how I can solve it. I wanted to understand why after
> installing the pg 9.6 packages suddenly psql tries to access the socket on
> /var/run/postgresql. Does the libpq default unix
>
>
> You could also try pg_test_fsync to get low-level information, to
>> supplement the high level you get from pgbench.
>
>
> Thanks for pointing me to this tool. never knew pg_test_fsync existed!
> I've run `pg_test_fsync -s 60` two times and this is the output -
>
On Thu, Apr 4, 2019 at 3:42 AM Duncan Kinnear
wrote:
>
> the EXPLAIN (ANALYSE, TIMING TRUE) of this query gives:
>
> Update on next_id (cost=0.14..8.16 rows=1 width=36) (actual
> time=0.057..0.057 rows=0 loops=1)
>-> Index Scan using next_id_pk on next_id (cost=0.14..8.16 rows=1
>
On Mon, Feb 25, 2019 at 11:13 AM Gunther Schadow
wrote:
> Anyway, I think the partitioned table is the right and brilliant solution,
> because an index really isn't required. The actual pending partition will
> always remain quite small, and being a queue, it doesn't even matter how
> big it
On Sat, Feb 23, 2019 at 4:06 PM Gunther wrote:
> Hi,
>
> I am using an SQL queue for distributing work to massively parallel
> workers.
>
You should look into specialized queueing software.
...
> I figured I might just pause all workers briefly to schedule the REINDEX
> Queue command, but the
On Mon, Feb 25, 2019 at 3:54 AM kimaidou wrote:
> Wich strikes me is that if I try to simplify it a lot, removing all data
> but the main table (occtax.observation) primary key cd_nom and aggregate,
> the query plan should be able tu use the cd_nom index for sorting and
> provide better query
On Sun, Feb 24, 2019 at 1:02 PM Gunther wrote:
> Thank you all for responding so far.
>
> David Rowley and Justin Pryzby suggested things about autovacuum. But I
> don't think autovacuum has any helpful role here. I am explicitly doing a
> vacuum on that table. And it doesn't help at all.
On Sat, Feb 23, 2019 at 4:06 PM Gunther wrote:
> the dequeue operation is essentially this:
>
> BEGIN
>
> SELECT jobId, action
> FROM Queue
> WHERE pending
> FOR UPDATE SKIP LOCKED
>
>
There is no LIMIT shown. Wouldn't the first thread to start up just lock
all the rows and everyone else
On Thu, Mar 14, 2019 at 3:29 AM Stephan Schmidt wrote:
> Hello,
>
>
>
> i’m currently working on a high Performance Database and want to make sure
> that whenever there are slow queries during regular operations i’ve got all
> Information about the query in my logs. So auto_explain come to mind,
On Thu, Feb 7, 2019 at 6:55 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
I have 3 questions :
> 1)To what value do you recommend to increase the vacuum cost_limit ? 2000
> seems reasonable ? Or maybe its better to leave it as default and assign a
> specific value for big tables ?
>
On Wed, Feb 6, 2019 at 5:29 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> Now the question is how to handle or tune it ? Is there any change that I
> need to increase the cost_limit / cost_delay ?
>
Sometimes vacuum has more work to do, so it takes more time to do it.
There is
On Wed, Feb 6, 2019 at 9:42 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> Well, basically I'm trying to tune it because the table still keep
> growing. I thought that by setting the scale and the threshold it will be
> enough but its seems that it wasnt. I attached some of the logs
On Tue, Feb 12, 2019 at 10:42 AM Tom Lane wrote:
> Thomas Kellerer writes:
> > The bloom index is only used if either Seq Scan is disabled or if the
> random_page_cost is set to 1 (anything about 1 triggers a Seq Scan on my
> Windows laptop).
>
> Hm. blcostestimate is using the default cost
On Tue, Feb 12, 2019 at 11:58 AM Jeff Janes wrote:
>
> On Tue, Feb 12, 2019 at 10:42 AM Tom Lane wrote:
>
>>
>> Hm. blcostestimate is using the default cost calculation, except for
>>
>> /* We have to visit all index tuples anyway */
>>
On Tue, Feb 12, 2019 at 4:23 AM Jung, Jinho wrote:
>
> Hello,
>
> We are developing a tool called sqlfuzz for automatically finding
> performance regressions in PostgreSQL. sqlfuzz performs mutational fuzzing
> to generate SQL queries that take more time to execute on the latest
> version of
On Tue, Feb 12, 2019 at 4:17 PM Tom Lane wrote:
> Jeff Janes writes:
> > In order for bloom (or any other users of CREATE ACCESS METHOD, if there
> > are any) to have a fighting chance to do better, I think many of
> selfuncs.c
> > currently private functions would hav
On Mon, Jan 28, 2019 at 12:03 AM Saurabh Nanda
wrote:
> All this benchmarking has led me to a philosophical question, why does PG
> need shared_buffers in the first place?
>
PostgreSQL cannot let the OS get its hands on a dirty shared buffer until
the WAL record "protecting" that buffer has
On Tue, Jan 29, 2019 at 6:39 AM Saurabh Nanda
wrote:
> Hi,
>
> I'm going crazy trying to optimise my Postgres config for a production
> setting [1] Once I realised random changes weren't getting my anywhere, I
> finally purchased PostgreSQL 10 - Higher Performance [2] and understood the
> impact
On Sun, Apr 14, 2019 at 9:06 PM Gunther wrote:
> Thanks for looking at my problem Tom Lane and Jeff Janes. Sorry for not
> having given enough detail.
>
> The version is 10.2 latest. The database was originally built with 10.1
> and then just started with 10.2.
>
Do you mean
On Sun, Apr 14, 2019 at 4:51 PM Gunther wrote:
> For weeks now, I am banging my head at an "out of memory" situation. There
> is only one query I am running on an 8 GB system, whatever I try, I get
> knocked out on this out of memory.
>
Is PostgreSQL throwing an error with OOM, or is getting
On Sun, Apr 14, 2019 at 11:04 PM Gunther wrote:
> Could you rerun the query with \set VERBOSITY verbose to show the file/line
> that's failing ?
>
> Here goes:
>
> integrator=# \set VERBOSITY verbose
> integrator=# SET ENABLE_NESTLOOP TO OFF;
> SET
> integrator=# INSERT INTO
On Sun, Apr 14, 2019 at 11:59 PM Gunther wrote:
> Is there any doubt that this might be a problem with Linux? Because if
> you want, I can whip out a FreeBSD machine, compile pgsql, and attach
> the same disk, and try it there. I am longing to have a reason to move
> back to FreeBSD anyway. But
On Mon, Apr 15, 2019 at 12:34 PM Gunther wrote:
> Breakpoint 2, AllocSetAlloc (context=0x1168230, size=8272) at aset.c:715
> 715 {
> (gdb) p context->name
> $8 = 0x96ce5b "ExecutorState"
>
>
I think that the above one might have been the one you wanted.
> I guess I should run this for a
On Mon, Apr 15, 2019 at 11:28 AM Tom Lane wrote:
> Jeff Janes writes:
> > To get it to happen faster, maybe you could run the server with a small
> > setting of "ulimit -v"? Or, you could try to capture it live in gdb.
> > Unfortunately I don't know how to s
On Mon, Apr 15, 2019 at 9:49 PM Gunther wrote:
> Jeff Janes had more
>
> Breakpoint 2, AllocSetAlloc (context=0x1168230, size=8272) at aset.c:715
>> 715 {
>> (gdb) p context->name
>> $8 = 0x96ce5b "ExecutorState"
>>
>>
> I thin
On Thu, May 30, 2019 at 11:08 AM Haroldo Kerry wrote:
> Hello,
>
> We are migrating our PostgreSQL 9.6.10 database (with streaming
> replication active) to a faster disk array.
> We are using this opportunity to enable checksums, so we will have to do a
> full backup-restore.
> The database size
On Mon, May 6, 2019 at 11:38 AM Vitaly Baranovsky
wrote:
> Thank you, Jeff!
>
> We'll be looking forward to the next version of Postgres in this case.
>
> As far as I understand, you've answered about sending filtering condition
> to a foreign server... Could you, please, clarify about another
On Mon, May 6, 2019 at 10:44 AM Vitaly Baranovsky
wrote:
> Hello all,
>
> I faced strange behavior of PostgreSQL during the query execution.
>
...
> Also, please, note, that SQL without WHERE clause has been set to the
> foreign server:
> " Remote SQL: SELECT primary_uuid FROM
On Mon, May 6, 2019 at 11:53 AM Jeff Janes wrote:
> On Mon, May 6, 2019 at 11:38 AM Vitaly Baranovsky <
> barvetalfor...@gmail.com> wrote:
>
>> Thank you, Jeff!
>>
>> We'll be looking forward to the next version of Postgres in this case.
>>
>>
On Sat, Apr 20, 2019 at 4:26 PM Tom Lane wrote:
> Tomas Vondra writes:
> > Considering how rare this issue likely is, we need to be looking for a
> > solution that does not break the common case.
>
> Agreed. What I think we need to focus on next is why the code keeps
> increasing the number of
On Sat, Apr 20, 2019 at 4:48 PM Tom Lane wrote:
> Gunther writes:
> > and checked my log file and there was nothing before the call
> > MemoryContextStats(TopPortalContext) so I don't understand where this
> > printf stuff is ending up.
>
> It's going to stdout, which is likely block-buffered
On Tue, Aug 20, 2019 at 11:12 AM Felix Geisendörfer
wrote:
...
> [1] My actual query had bad estimates for other reasons (GIN Index), but
> that's another story. The query above was of course deliberately designed
> to have bad estimates.
>
As noted elsewhere, v12 thwarts your attempts to
On Thu, Aug 22, 2019 at 1:09 AM Pavel Stehule
wrote:
> čt 22. 8. 2019 v 3:11 odesílatel Jeff Janes napsal:
>
>> ...
> But the same advance in v12 which makes it harder to fool with your test
>> case also opens the possibility of fixing your real case.
>>
>
>
On Mon, Aug 26, 2019 at 4:26 AM Barbu Paul - Gheorghe <
barbu.paul.gheor...@gmail.com> wrote:
> On Sun, Aug 25, 2019 at 5:51 PM Jeff Janes wrote:
> >
> > Yes, it certainly looks like it is due to cold caches. But you say it
> is slow at first, and then say it var
On Wed, Sep 4, 2019 at 1:30 PM Fredrik Blomqvist <
fredrik.blomqvist...@gmail.com> wrote:
> Hi,
>
> I have tried doing some research for quite a while on the performance
> implications of the built-in upsert (INSERT ... ON CONFLICT UPDATE...) when
> a lot of upserts are made. The scale is
On Fri, Aug 9, 2019 at 4:42 AM Rob Emery wrote:
>
> It
> seems to me like the Bitmap Heap Scan on proposal is the issue because
> the recheck is throwing away enormous amounts of data.
Have you tried increasing work_mem? The probable reason for the recheck is
that your bitmap overflows the
On Mon, Nov 4, 2019 at 3:38 PM Scott Rankin wrote:
> Definitely no long-running transactions on this table;
>
Any long running transactions at all? The lock on the table is only
necessary to explain why the problem would have gone away at the same time
as the reindex finished. If there is a
On Mon, Nov 4, 2019 at 2:38 PM Scott Rankin wrote:
> Hello all,
>
>
>
> We are trying to debug some slow performance in our production environment
> (Amazon RDS, Postgresql 9.6.11), and we’re looking at a particular EXPLAIN
> node that seems… weird. This is a very large query involving a number
On Wed, Oct 30, 2019 at 12:25 PM Alessandro Baretta
wrote:
> -> Bitmap Index Scan on idx_object
> (cost=0.00..75580.00 rows=1 width=0) (actual time=24.094..24.094
> rows=77 loops=1)
>Index Cond: ((... @> ...::jsonb) AND (...
> @> ...::jsonb))
>
On Wed, Nov 13, 2019 at 6:56 AM Marco Colli wrote:
> > the answer is that is because it is a GIN index. Make the same index
> only as btree, and you should get good performance as it can filter the
> tags within a given project without visiting the table.
>
> Currently I have this GIN index:
>
On Wed, Nov 13, 2019 at 4:20 AM Marco Colli wrote:
> Replying to the previous questions:
> - work_mem = 64MB (there are hundreds of connections)
> - the project 123 has more than 7M records, and those that don't have the
> tag 'en' are 4.8M
>
>
>> What was the plan for the one that took 500ms?
>
On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck <
luisrobe...@siscobra.com.br> wrote:
>
>
> Indeed, reducing the costs made the query run in parallel, but the
> improvement in speed was not worth the cost (CPU).
>
Could you show the plan for that?
On Wed, Nov 13, 2019 at 3:11 PM Luís Roberto Weck <
luisrobe...@siscobra.com.br> wrote:
> Hi!
>
> Is there a reason query 3 can't use parallel workers? Using q1 and q2
> they seem very similar but can use up to 4 workers to run faster:
>
> q1: https://pastebin.com/ufkbSmfB
> q2:
On Wed, Nov 13, 2019 at 4:01 PM Luís Roberto Weck <
luisrobe...@siscobra.com.br> wrote:
>
> Maybe PostgreSQL can't find a way to calculate having estimates?
>
I wasn't even thinking of the HAVING estimates I was thinking of just the
raw aggregates. It can't implement the HAVING until has the
>
>
> 3) Here's the query plan that I get after disabling the seq scan:
>
>
> QUERY PLAN
>
>
>
> ---
On Thu, Nov 14, 2019 at 5:20 PM Craig James wrote:
> I'm completely baffled by this problem: I'm doing a delete that joins
> three modest-sized tables, and it gets completely stuck: 100% CPU use
> forever. Here's the query:
>
>
> Aggregate (cost=193.54..193.55 rows=1 width=8)
> -> Nested
On Fri, Nov 15, 2019 at 7:27 PM Craig James wrote:
> On Fri, Nov 15, 2019 at 2:45 PM Jeff Janes wrote:
> BTW, I'll note at this point that "analyze category_staging_8" prior to
> this query made no difference.
>
Isn't that the wrong table to have analyzed? The offend
On Mon, Dec 2, 2019 at 8:29 AM Aufar Gilbran wrote:
> Hello,
>
> I'm trying to figure out how to optimise 3-table (many-to-many relation)
> joins
> with predicate, limit, and ordering, where one of the tables returns at
> most one
> row.
>
> This is the query that I have right now:
>
> SELECT
On Fri, Dec 6, 2019 at 12:50 PM MichaelDBA wrote:
> And Just to reiterate my own understanding of this...
>
> autovacuum priority is less than a user-initiated request, so issuing a
> manual vacuum (user-initiated request) will not result in being cancelled.
>
Somethings happen in some
On Fri, Nov 29, 2019 at 11:06 AM Florian Philippon <
florian.philip...@doctolib.com> wrote:
>
> We tried another solution: we loaded a minimal schema (without indexes and
> constraints) on the subscriber and created the subscription. The initial
> copy phase was way faster (a few hours). Then we
On Tue, Dec 17, 2019 at 8:08 AM Laurenz Albe
wrote:
> On Mon, 2019-12-16 at 15:50 -0500, Tom Lane wrote:
> > Peter Geoghegan writes:
> > > Why do the first and the twentieth executions of the query have almost
> > > identical "buffers shared/read" numbers? That seems odd.
> >
> > It's repeat
On Tue, Dec 10, 2019 at 3:40 AM Fahiz Mohamed wrote:
> Thank you very much for your prompt responses.
>
> I have analysed more regarding this and found the long running query.
>
> I ran "explain analyse" on this query and I got following result. (We have
> 2 identical DB instances and they
On Thu, Dec 5, 2019 at 5:26 PM Mike Schanne wrote:
> Hi,
>
> I am investigating a performance problem in our application and am seeing
> something unexpected in the postgres logs regarding the autovacuum.
>
>
>
> 2019-12-01 13:05:39.029
>
On Fri, Dec 6, 2019 at 10:55 AM Mike Schanne wrote:
> The error is not actually showing up very often (I have 8 occurrences from
> 11/29 and none since then). So maybe I should not be concerned about it.
> I suspect we have an I/O bottleneck from other logs (i.e. long checkpoint
> sync times),
On Wed, Dec 11, 2019 at 5:21 PM Fahiz Mohamed wrote:
> There is a slight different in both instance’s data. Inastanbce 1 contains
> latest data and instance 2 consists of data which is 3 weeks older than
> instance 1.
>
In knowing where to look for differences in performance, there is a big
On Tue, Dec 10, 2019 at 11:43 AM Guillaume Lelarge
wrote:
This query uses the column statistics to estimate bloat. AFAIK, json
> columns don't have statistics, so the estimation can't be relied on (for
> this specific table at least).
>
This was true prior to 9.5 (for xml at least, I don't know
On Mon, Oct 14, 2019 at 1:25 PM dangal wrote:
> Do you think it should increase bgwriter_lru_maxpages due to the value of
> maxwritten_clean?
>
I find the background writer to be pretty unimportant these days. If the
kernel is freely accepting writes without blocking, the backends can
probably
On Sun, Nov 24, 2019 at 1:05 PM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> Hey Jeff,
> This example was only used to show that pg96 had better perfomance than
> pg12 in a very simple case.
>
OK, but do you agree that a 15% slow down is more realistic than 3 fold
one? Or are you
On Sun, Nov 24, 2019 at 8:52 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> Hey Andrew,
> It seems that changing this parameter worked for me.
> Setting it to zero means that there wont be any parallel workers for one
> query right ?
> Is it something familiar this problem with the
On Thu, Oct 10, 2019 at 3:40 AM Ogden Brash wrote:
> If each of the tables has about 3+ billion rows, the index is still going
> to be pretty large and spread over many files. In the source database that
> was backed up, the primary key sequence was sequentially assigned and
> written, but as
On Wed, Oct 9, 2019 at 10:56 AM Yavuz Selim Sertoğlu (ETIYA) <
yavuz.serto...@etiya.com> wrote:
> Thanks for the reply Tom,
>
> Sorry, I couldn't understand. I just copied inside of view and add
> conditions from query that runs with view.
> The comma parts are the same in two queries, one is
On Tue, Oct 8, 2019 at 7:37 PM Arya F wrote:
> As my table has gotten bigger, it takes longer to get a single row back
> when querying a row by its btree index.
>
>
Is this in a probabilistic sense, they take longer on average, or has every
single access gotten slower? If the increase in size
On Wed, Oct 9, 2019 at 4:33 AM Ogden Brash wrote:
> # lsof -p 6600 | wc -l;
> 840
>
> # lsof -p 6601 | wc -l;
> 906
>
> Is that normal? That there be so many open file pointers? ~900 open file
> pointers for each of the processes?
>
I don't think PostgreSQL makes any effort to conserve file
On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby wrote:
> On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote:
> > With seqscan disabled, I get this plan on 9.6:
> > Bitmap Heap Scan on friend (cost=8.42..19.01 rows=14 width=8)
> ...
> > I expected to get an index-only scan in this
On Fri, Dec 20, 2019 at 1:58 PM Tom Lane wrote:
> Jeff Janes writes:
> > The docs for parallel_tuple_cost are quite terse, as the reference
> section
> > of the docs usually are:
> > "Sets the planner's estimate of the cost of transferring one tuple from a
> >
On Wed, Dec 18, 2019 at 4:53 AM James(王旭) wrote:
> Hello,
>>
>> I encountered into this kernel message, and I cannot login into the Linux
>> system anymore:
>
>
>>
>> Dec 17 23:01:50 hq-pg kernel: sh (6563): drop_caches: 1
>>
>> Dec 17 23:02:30 hq-pg kernel: INFO: task sync:6573 blocked for more
The docs for parallel_tuple_cost are quite terse, as the reference section
of the docs usually are:
"Sets the planner's estimate of the cost of transferring one tuple from a
parallel worker process to another process. The default is 0.1."
Usually you can find more extensive discussion of such
1 - 100 of 166 matches
Mail list logo