Re: Setting effective_io_concurrency in VM?

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

Re: insert and query performance on big string table with pg_trgm

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

Re: Bitmap scan is undercosted? - boolean correlation

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

Re: Bitmap scan is undercosted? - boolean correlation

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

Re: Bitmap scan is undercosted?

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 >

Re: Sort is generating rows

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,

Re: Batch insert heavily affecting query performance.

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

Re: primary key hash index

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,

Re: Query is slow when run for first time; subsequent execution is fast

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. > >

Re: Query is slow when run for first time; subsequent execution is fast

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

Re: effective_io_concurrency on EBS/gp2

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

Re: Performance difference in accessing differrent columns in a Postgres Table

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 >

Re: Performance difference in accessing differrent columns in a Postgres Table

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

Re: Performance difference in accessing differrent columns in a Postgres Table

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

Re: Performance difference in accessing differrent columns in a Postgres Table

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.

Re: Bi-modal streaming replication throughput

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

Re: Improving Performance of Query ~ Filter by A, Sort by B

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?

Re: Why HDD performance is better than SSD in this case

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

Re: Improving Performance of Query ~ Filter by A, Sort by B

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

Re: Performance difference in accessing differrent columns in a Postgres Table

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

Re: Multi-second pauses blocking even trivial activity

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

Re: Multi-second pauses blocking even trivial activity

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

Re: Memory size

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

Re: Gained %20 performance after disabling bitmapscan

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

Re: Gained %20 performance after disabling bitmapscan

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

Re: High CPU Usage of "SET ROLE"

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;"): > > ... >

Re: trying to delete most of the table by range of date col

> > 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

Re: Optimizer choosing the wrong plan

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

Re: Query Performance Issue

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

Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

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

Re: Optimizer choosing the wrong plan

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

Re: PostgreSQL Read IOPS limit per connection

> > > *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

Re: postgresql unix socket connections

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

Re: postgresql unix socket connections

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

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

> > > 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 - >

Re: Commit(?) overhead

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 >

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

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

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

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

Re: Aggregate and many LEFT JOIN

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

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

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.

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

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

Re: impact of auto explain on overall performance

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,

Re: autovacuum big table taking hours and sometimes seconds

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 ? >

Re: autovacuum big table taking hours and sometimes seconds

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

Re: autovacuum big table taking hours and sometimes seconds

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

Re: Bloom index cost model seems to be wrong

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

Re: Bloom index cost model seems to be wrong

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 */ >>

Re: Performance regressions found using sqlfuzz

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

Re: Bloom index cost model seems to be wrong

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

Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

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

Re: Will higher shared_buffers improve tpcb-like benchmarks?

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

Re: Out of Memory errors are frustrating as heck!

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

Re: Out of Memory errors are frustrating as heck!

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

Re: Out of Memory errors are frustrating as heck!

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

Re: Out of Memory errors are frustrating as heck!

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

Re: Out of Memory errors are frustrating as heck!

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

Re: Out of Memory errors are frustrating as heck!

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

Re: Out of Memory errors are frustrating as heck!

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

Re: Shortest offline window on database migration

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

Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)

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

Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)

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

Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)

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. >> >>

Re: Out of Memory errors are frustrating as heck!

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

Re: Out of Memory errors are frustrating as heck!

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

Re: Extremely slow HashAggregate in simple UNION query

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

Re: Extremely slow HashAggregate in simple UNION query

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. >> > >

Re: Erratically behaving query needs optimization

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

Re: Upsert performance considerations (~1 mil/hour)

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

Re: Bitmap heap scan performance

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

Re: Huge shared hit for small table

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

Re: Huge shared hit for small table

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

Re: GIN index on JSONB not used due to lack of nested statistics

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)) >

Re: Slow "not in array" operation

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: >

Re: Slow "not in array" operation

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? >

Re: Parallel Query

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?

Re: Parallel Query

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:

Re: Parallel Query

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

Re: Slow "not in array" operation

> > > 3) Here's the query plan that I get after disabling the seq scan: > > > QUERY PLAN > > > > ---

Re: Simple DELETE on modest-size table runs 100% CPU forever

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

Re: Simple DELETE on modest-size table runs 100% CPU forever

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

Re: [External] Join queries slow with predicate, limit, and ordering

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

Re: autovacuum locking question

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

Re: Logical replication performance

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

Re: Consecutive Query Executions with Increasing Execution Time

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

Re: Specific query taking time to process

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

Re: autovacuum locking question

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 >

Re: autovacuum locking question

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),

Re: Specific query taking time to process

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

Re: unexpected result for wastedbytes query after vacuum full

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

Re: pg_stat_bgwriter

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

Re: performance degredation after upgrade from 9.6 to 12

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

Re: performance degredation after upgrade from 9.6 to 12

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

Re: Modification of data in base folder and very large tables

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

Re: Query slows when used with view

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

Re: Would SSD improve Index Only Scan performance by a lot?

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

Re: Modification of data in base folder and very large tables

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

Re: Optimising a two column OR check

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

Re: How to set parallel_tuple_cost

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 > >

Re: How to prevent POSTGRES killing linux system from accepting too much inserts?

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

How to set parallel_tuple_cost

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   2   >