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

On Mon, Nov 20, 2017 at 2:54 PM, Matthew Hall wrote: While I have not done exhaustive testing, from the tests I have done I've never found gist to be better than gin with trgm indexes. > > Here is the table: > >Unlogged table "public.huge_table" >Column

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 future will require the d

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 > with the setting. This

Re: Bitmap scan is undercosted?

On Fri, Dec 1, 2017 at 3:54 PM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > On 02/12/2017 01:11, Justin Pryzby wrote: > >> I tried to reproduce this issue and couldn't, under PG95 and 10.1: >> >> On Fri, Dec 01, 2017 at 12:34:27PM -0600, Justin Pryzby wrote: >> >>> On Fri, Dec 01, 201

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 > time=82.212

Re: Bitmap scan is undercosted?

On Sat, Dec 2, 2017 at 3:44 PM, Tom Lane wrote: > Jeff Janes writes: > > On Fri, Dec 1, 2017 at 11:08 PM, Vitaliy Garnashevich < > > vgarnashev...@gmail.com> wrote: > >> # x4 tuple/operator costs - bitmap scan still a bit cheaper > >> set seq_page_cost

Re: Bitmap scan is undercosted? - boolean correlation

On Sat, Dec 2, 2017 at 8:04 PM, Justin Pryzby 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 flag column. > > It

Re: Bitmap scan is undercosted? - boolean correlation

On Dec 3, 2017 15:31, "Tom Lane" wrote: Jeff Janes writes: > On Sat, Dec 2, 2017 at 8:04 PM, Justin Pryzby wrote: >> It thinks there's somewhat-high correlation since it gets a list of x >> and y values (integer positions by logical and physical sort order) and

Re: Bitmap scan is undercosted?

On Sun, Dec 3, 2017 at 1:15 PM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > On 02/12/2017 23:17, Jeff Janes wrote: > > Right, so there is a cpu costing problem (which could only be fixed by > hacking postgresql and recompiling it), but it is much smaller of a pro

Re: Bitmap scan is undercosted? - boolean correlation

On Tue, Dec 5, 2017 at 10:50 AM, Tom Lane wrote: > Jeff Janes writes: > > On Dec 3, 2017 15:31, "Tom Lane" wrote: > >> Jeff Janes writes: > >>> But I do see that ties within the logical order of the column values > are > >>> broken to ag

Re: Bitmap scan is undercosted?

On Tue, Dec 5, 2017 at 11:06 PM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: This is very cool, thanks. > I've tried to create a better test case: > - Increase shared_buffers and effective_cache_size to fit whole database, > including indexes. > - Use random(), to avoid correlation b

Re: Bitmap scan is undercosted? - overestimated correlation and cost_index

On Wed, Dec 6, 2017 at 1:46 PM, Justin Pryzby wrote: > On Tue, Dec 05, 2017 at 01:50:11PM -0500, Tom Lane wrote: > > Jeff Janes writes: > > > On Dec 3, 2017 15:31, "Tom Lane" wrote: > > >> Jeff Janes writes: > > >>> But I do see that t

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 provisioning? Cheers, Je

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, regarding uuid insert, shown > t

Re: Need Help on wal_compression

On Tue, Jan 9, 2018 at 1:53 AM, Rambabu V wrote: > Hi Team, > > Daily 4000 Archive files are generating and these are occupying more > space, we are trying to compress wall files with using wal_compression > parameter, but we are not seeing any change in wal files count, could you > please help u

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. > > https://www.postgresql.org/message

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 the first time. Only >

Re: need help on memory allocation

On Tue, Jan 23, 2018 at 5:59 AM, Rambabu V wrote: > > cat PostgreSQL-2018-01-23_06.csv|grep FATAL What about ERROR, not just FATAL? Or grep for "out of memory" >> *$ free -mh* >> total used free sharedbuffers cached >> Mem: 58G58G

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 dif

Re: Memory size

On Sun, Mar 11, 2018 at 5:48 AM, dangal wrote: > > Dear some consultation, I have a base of about 750 GB in size and we are > having problem of slowness in certain views of the application, so I have > been seeing it is apparently a memory problem because if I run again the > view runs fast, the

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 shared buffer > The server had 1

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

Re: significant jump in sql statement timing for on server vs a remote connection

On Tue, Apr 19, 2022 at 5:00 PM Sbob wrote: > > However if we move the file to another server in the same network and > run with a psql -h then it runs for more than 10min. What is the ping time? Packet loss? You can't take for granted that the network is good and fast just because they are on

Re: Postgresql TPS Bottleneck

On Wed, Apr 20, 2022 at 5:13 AM wrote: > > The next thing I did was starting two independent Postgres instances on > the same server and run independent client applications against each of > them. This resulted in our application getting almost double of the TPS > compared to running a single ins

Re: Array of integer indexed nested-loop semi join

On Wed, Apr 27, 2022 at 8:19 AM Mickael van der Beek < mickael.van.der.b...@gmail.com> wrote: > > The last query does not finish after waiting for more than 15 minutes. > (The temporary view creation is very fast and required due to the same > query in a CTE greatly reducing performance (by more t

Re: Window partial fetch optimization

On Tue, May 3, 2022 at 2:11 PM Levi Aul wrote: > I have a “temporal table” — a table where there are multiple “versions” of > entities, with each version having a distinct timestamp: > CREATE TABLE contract_balance_updates ( > block_id bigint NOT NULL, > block_signed_at timestamp(0) witho

Re: Why is there a Sort after an Index Only Scan?

On Wed, May 4, 2022 at 7:15 PM André Hänsel wrote: > Quick(?) question... why is there a Sort node after an Index Only Scan? > Shouldn't the index already spit out sorted tuples? > > CREATE INDEX ON orders_test(shipping_date, order_id); > > EXPLAIN ANALYZE SELECT > FROM orders_test > WHERE TRUE >

Re: Array of integer indexed nested-loop semi join

On Fri, May 20, 2022 at 6:42 AM Mickael van der Beek < mickael.van.der.b...@gmail.com> wrote: > > Query: > > EXPLAIN ( >> ANALYZE, >> VERBOSE, >> COSTS, >> BUFFERS, >> TIMING >> ) >> SELECT >> fu.w2_page_idxs >> FROM >> fact_users >> AS fu >> WHERE >> EXISTS ( >> SELECT >>

Re: Array of integer indexed nested-loop semi join

On Mon, May 23, 2022 at 3:57 AM Mickael van der Beek < mickael.van.der.b...@gmail.com> wrote: > Hello Jeff, > > Sadly, the query you suggested won't work because you are only returning > the first row of the matching inner query rows. > Sure, but the query I replaced did the same thing. (I thoug

Re: REINDEXdb performance degrading gradually PG13.4

On Tue, May 31, 2022 at 11:14 AM Praneel Devisetty < devisettypran...@gmail.com> wrote: > > Hi, >> >> We are trying to reindex 600k tables in a single database of size 2.7TB >> using reindexdb utility in a shell script >> reindexdb -v -d $dbname -h $hostname -U tkcsowner --concurrently -j >> $par

Re: rows selectivity overestimate for @> operator for arrays

On Fri, May 27, 2022 at 12:19 PM Alexey Ermakov < alexey.erma...@dataegret.com> wrote: > Hello, please look into following example: > > postgres=# create table test_array_selectivity as select > array[id]::int[] as a from generate_series(1, 1000) gs(id); > SELECT 1000 > postgres=# explain

Re: reindex option for tuning load large data

On Fri, Jun 17, 2022 at 1:34 AM James Pang (chaolpan) wrote: > Hi , > > We plan to migrate large database from Oracle to Postgres(version 13.6, > OS Redhat8 Enterprise), we are checking options to make data load in > Postgres fast. Data volume is about several TB, thousands of indexes, > many

Re: Postgresql 14 partitioning advice

On Wed, Jul 27, 2022 at 8:55 AM Rick Otten wrote: > > One person I talked to said "try not to have more than 100 partitions", > even with the latest postgresql you'll end up with a lot of lock contention > if you go over 100 partitions. > > It is hard to know how seriously to take the advice of a

Re: Catching up with performance & PostgreSQL 15

On Tue, Nov 29, 2022 at 4:07 PM David Rowley wrote: > On Wed, 30 Nov 2022 at 03:31, Tom Lane wrote: > > > > Alvaro Herrera writes: > > > IMO it was a mistake to turn JIT on in the default config, so that's > one > > > thing you'll likely want to change. > > > > I wouldn't necessarily go quite t

Re: LIKE CLAUSE on VIEWS

On Sun, Jan 22, 2023 at 6:34 AM aditya desai wrote: > Hi, > Is there any way to improve performance of LIKE clause on VIEWS. > > select * From request_vw where upper(status) like '%CAPTURED%' - 28 > seconds. > You would need to have an expression index over upper(status) to support such a query,

Re: Performance of UPDATE operation

On Mon, Feb 13, 2023 at 10:09 AM Mkrtchyan, Tigran wrote: > > 0.524 0 BEGIN; > 0.819 0 INSERT INTO t_inodes (inumber, icrtime, > igeneration) > 0.962 0 UPDATE t_inodes SET igeneration = igeneration > + 1 where inumber = :inumber; >

Re: Connection forcibly closed remote server error.

On Wed, Feb 15, 2023 at 7:13 AM aditya desai wrote: > Hi, > We are getting this error when transferring data using COPY command or > running workflow for huge data. We are using Password Authentication(LDAP) > > "Connection forcibly closed remote server" > Are you sure that that is the exact wor

Re: Planner choosing nested loop in place of Hashjoin

On Tue, Mar 7, 2023 at 7:14 AM Praneel Devisetty wrote: > Hi, > > I have a query which is taking roughly 10mins to complete and the query > planner is choosing a nested loop. > > query and query plan with analyze,verbose,buffers > qsEn | explain.depesz.com

Re: thousands of CachedPlan entry per backend

On Thu, Jun 1, 2023 at 4:51 AM James Pang (chaolpan) wrote: > 2) from this line, we saw total 42 blocks ,215 chunks > CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free (215 chunks); > 7715408 used, > > But from sum of it’s child level entrys, total sum(child lines) > block ,t

Re: Plan weirdness. A sort produces more rows than the node beneath it

On Fri, Aug 4, 2023 at 11:00 AM Dane Foster wrote: > Hello, > > I'm trying to understand a bit of weirdness in a plan output. There is a > sort node above a sequential scan node where the scan node produces 26,026 > rows yet the sort node above it produces 42,995,408. How is it possible > to sort

Re: slow delete

On Tue, Aug 15, 2023 at 4:23 PM Les wrote: { > > "Trigger Name": "RI_ConstraintTrigger_a_75463", > > "Constraint Name": "fk_pfq_src_product_file", > > "Relation": "product_file", > > "Time": 11179.429, > > "Calls": 90 > > }, > ... > The one with fk_pfft_product looks like this, it has about 500

Re: Queries containing ORDER BY and LIMIT started to work slowly

On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag wrote: > I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. > Everything was fine several days ago even with standard Postgresql > settings. I dumped a database with the compression option (maximum > compression level -Z 9) in order t

Re: Index bloat and REINDEX/VACUUM optimization for partial index

On Mon, Aug 28, 2023 at 8:33 PM jayaprabhakar k wrote: > Hi, > > TL;DR: > Observations: > >1. REINDEX requires a full table scan > - Roughly create a new index, rename index, drop old index. > - REINDEX is not incremental. running reindex frequently does not > reduce the fut

Re: Queries containing ORDER BY and LIMIT started to work slowly

On Tue, Aug 29, 2023 at 2:55 PM Rondat Flyag wrote: > I took the dump just to store it on another storage (external HDD). I > didn't do anything with it. > I don't see how that could cause the problem, it is probably just a coincidence. Maybe taking the dump held a long-lived snapshot open whic

Re: Queries containing ORDER BY and LIMIT started to work slowly

On Wed, Aug 30, 2023 at 1:31 PM Rondat Flyag wrote: > Hi and thank you for the response. > > I tried VACUUM ANALYZE for three tables, but without success. I also tried > to set enable_seqscan=off and the query took even more time. If I set > enable_sort=off then the query takes a lot of time and

Re: Index bloat and REINDEX/VACUUM optimization for partial index

On Wed, Aug 30, 2023 at 8:43 PM jayaprabhakar k wrote: > > > On Tue, Aug 29, 2023, 12:43 PM Jeff Janes wrote: > >> On Mon, Aug 28, 2023 at 8:33 PM jayaprabhakar k >> wrote: >> >>> >>> Since we are only interested in

Re: Index bloat and REINDEX/VACUUM optimization for partial index

On Thu, Aug 31, 2023 at 11:06 AM Maxim Boguk wrote: > With the default value of autovacuum_vacuum_scale_factor (The default is > 0.2 (20% of table size).) index will collect like 100M outdated/dead index > entries before autovacuum kicks in and cleans them all (in a worst case), > and of course

Re: Dirty reads on index scan,

On Fri, Sep 22, 2023 at 5:44 AM Koen De Groote wrote: > Alright. > > So, if I want to speed up the query, apart from trying to vacuum it > beforehand, I suspect I've hit the limit of what this query can do? > It is more a limit on the system as a whole, not just one query. How is this table bei

Re: GIN JSONB path index is not always used

On Tue, Oct 17, 2023 at 10:09 AM Tomasz Szymański wrote: > - Database version: 11.18 That is pretty old. It is 3 bug-fix releases out of date even for its major version, and the major version itself is just about to reach EOL and is missing relevant improvements. - Plan when it uses an index >

Re: GIN JSONB path index is not always used

On Mon, Oct 23, 2023 at 6:33 AM Tomasz Szymański wrote: > Limit (cost=0.00..1184.30 rows=21 width=4) (actual > time=1567.136..1619.956 rows=1 loops=1) >-> Seq Scan on account_user (cost=0.00..256768.27 rows=4553 width=4) > (actual time=1567.135..1619.953 rows=1 loops=1) > It thinks the

Re: [EXTERNAL] Re: Performance down with JDBC 42

On Sun, Nov 5, 2023 at 11:20 AM Abraham, Danny wrote: > Thanks Laurenz, > > Traced two huge plans. They differ. > The fast one does use Materialize and Memoize (the psql). > Is there something in JDBC 42 that blocks these algoruthms? Directly blocking those is not likely. Maybe the way the dri

Re: Strange "actual time" in simple CTE

On Sat, Dec 2, 2023 at 11:50 AM Jean-Christophe Boggio < postgre...@thefreecat.org> wrote: > Hello, > > I am trying to optimize a complex query and while doing some explains, I > stumbled upon this : > >CTE cfg > -> Result (cost=2.02..2.03 rows=1 width=25) (actual > time=7167.478..7167.

Re: Parallel hints in PostgreSQL with consistent perfromance

On Wed, Dec 27, 2023 at 8:15 AM mohini mane wrote: > Hello Team, > I observed that increasing the degree of parallel hint in the SELECT > query did not show performance improvements. > Below are the details of sample execution with EXPLAIN ANALYZE > PostgreSQL doesn't have hints, unless you are

Re: Slow GroupAggregate and Sort

On Thu, Dec 28, 2023 at 12:03 PM Darwin Correa wrote: > > when run the query (query.sql) as you can see in explain (plan4_v3.txt) > citus take about 18s to run all fragments > Where is plan4_v3.txt? Is that hidden in some non-obvious way in one of your links? > but each fragment take at most

Re: Parallel hints in PostgreSQL with consistent perfromance

On Thu, Dec 28, 2023 at 7:47 AM mohini mane wrote: > Thank you for your response !! > I am experimenting with SQL query performance for SELECT queries on large > tables and I observed that changing/increasing the degree of parallel hint > doesn't give the expected performance improvement. > But

Re: Questions about "Output" in EXPLAIN ANALYZE VERBOSE

On Tue, Jan 2, 2024 at 1:29 PM Jerry Brenner wrote: > We are currently on 13.9. > Why not just use the latest minor release, 13.13? For security reasons, that is the only minor release of v13 you should be using anyway. I think it is a bit much to hope that people will spend their time for fre

Re: Parallel hints in PostgreSQL with consistent perfromance

> *1st time query executed with PARALLEL DEGREE 2 * > explain analyze select /*+* PARALLEL(A 2)* */ * from > test_compare_all_col_src1 A; > QUERY > PLAN > > -

Re: Slow GroupAggregate and Sort

On Mon, Jan 1, 2024 at 9:57 AM Darwin Correa wrote: > Hello, Happy New Year! I add my responses in blue. > > > > El Thu, 28 Dec 2023 13:06:18 -0500, *Jeff Janes > >* escribió > > I thought the point of sharding was to bring more CPU and RAM to bear than &g

Re: Slow query when pg_trgm is in inner lopp

On Wed, Jun 20, 2018 at 9:21 AM, Sasa Vilic wrote: > Query that we have finds all routes between two set of points. A set is a > dynamically/loosely defined by pattern given by the user input. So for > example > if user wants to find all routes between international airports in Austria > toward

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

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

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

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

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

On Tue, Sep 4, 2018 at 3:16 AM jimmy wrote: > On windows, how to put an entry in my db startup script to run this query > （pg_prewarm） immediately after startng the server, and let the query warm > the cache itself. > Starting with PostgreSQL version 11 (to be released soon), you can use pg_pre

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 scenario

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

On Wed, Sep 5, 2018 at 12:00 PM Jeff Janes wrote: > 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 >>

Re: query gets very slow when :jsonb ?& operator is used

On Thu, Sep 6, 2018 at 7:52 PM wrote: > I have also asked this question on Stackoverflow and DBA stack exchange > with no answer. It's a fairly long post, so I will post a link to it, as on > Stackoverflow it is formatted nicely > > > https://stackoverflow.com/questions/52212878/query-gets-very-s

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 bloc

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: To keep indexes in memory, is large enough effective_cache_size enough?

On Wed, Sep 19, 2018 at 5:19 AM Sam R. wrote: > Hi! > > Is is possible to force PostgreSQL to keep an index in memory? > It might be possible to put the indexes in a separate tablespace, then do something at the file-system level to to force the OS cache to keep pages for that FS in memory. >

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 u

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 configurati

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

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 a

Re: SQL Perfomance during autovacuum

On Wed, Dec 19, 2018 at 1:04 AM anand086 wrote: > > The Execution time for the above sql is 17841.467 ms during normal > operations but when autovacuum runs on table test_table, the same sql took > 1628495.850 ms (from the postgres log). > > We have noticed this increase in execution times for t

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 queue

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 c

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 running

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 soc

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 - > https://gist.github.com/saura

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

On Sun, Jan 27, 2019 at 2:39 AM Saurabh Nanda wrote: > >> PGOPTIONS="-c synchronous_commit=off" pgbench -T 3600 -P 10 >> >> >> I am currently running all my benchmarks with synchronous_commit=off and >> will get back with my findings. >> > > > It seems that PGOPTIONS="-c synchronous_commit=o

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 been

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

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

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 Post

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: 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: Why isn't an index scan being used?

On Tue, Feb 19, 2019 at 11:59 PM Abi Noda wrote: > Thanks Justin. > > The 4ms different in the examples isn't an accurate benchmark. I'm seeing > about a ~20% difference over a larger sample size. And this is on a fork of > the production database. > Please show the execution plans from that lar

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 w

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

  1   2   >