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

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

<    1   2