Re: Bitmap scan is undercosted?

On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote: > We recently had an issue in production, where a bitmap scan was chosen > instead of an index scan. Despite being 30x slower, the bitmap scan had > about the same cost as the index scan. Me too, see also:

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

On Fri, Dec 15, 2017 at 02:54:06PM -0600, Justin Pryzby wrote: > SSD: good question. > > Here's an rackspace VM with PG9.6.6, 2GB shared_buffers, 8GB RAM (~4GB of > which > is being used as OS page cache), and 32GB SSD (with random_page_cost=1). The > server is in use by our a

Re: Autoanalyze CPU usage

- We have an open schema and heavily depend on jsonb, so I'm not sure if > increasing the statistics target will be helpful. If the increased stats target isn't useful for that, I would recommend to decrease it. -- Justin Pryzby System Administrator Telsasoft +1-952-707-8581

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

On Fri, Dec 15, 2017 at 02:54:06PM -0600, Justin Pryzby wrote: > SSD: good question. > > Here's an rackspace VM with PG9.6.6, 2GB shared_buffers, 8GB RAM (~4GB of > which > is being used as OS page cache), and 32GB SSD (with random_page_cost=1). The > server is in use by our a

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

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 ties within the logical order of the column values are > >>>

Re: Bitmap scan is undercosted?

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, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote: > > We recently had an issue in production, where a bitmap scan was chosen &g

Re: Time bucketing query performance

On Mon, May 07, 2018 at 07:33:17PM -0400, Julian Wilson wrote: > Hi, > > I'm trying to debug improve the performance of my time bucketing query. > What I'm trying to do is essentially bucket by an arbitrary interval and > then do some aggregations within that interval (min,max,sum, etc). I am >

Re: Help with tuning slow query

On Fri, May 18, 2018 at 08:32:55PM +, Kotapati, Anil wrote: > We are facing issues with one of our query, when we use order by count it is > taking lot of time to execute the query. To be precise it is taking 9 min to > execute the query from table which has ~220 million records. Is there a

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

On Tue, May 22, 2018 at 03:51:44AM -0700, pavan95 wrote: > Please find the output of explain(analyze,buffers) for the whole query in > the below link. > Seq Scan on res_users users (cost=750.92..1,836.69 rows=249 width=15) (actual > time=3.962..17.544 rows=67 loops=1) Not sure but would you

Re: Queue table that quickly grows causes query planner to choose poor plan

On Wed, Jun 27, 2018 at 03:45:26AM +, David Wheeler wrote: > Hi All, > > I’m having performance trouble with a particular set of queries. It goes a > bit like this > > 1) queue table is initially empty, and very narrow (1 bigint column) > 2) we insert ~30 million rows into queue table > 3)

Re: Slow join

Hi, Thanks for providing all this info :) On Mon, Jun 25, 2018 at 05:55:49PM +0200, Roman Kushnir wrote: > Hello, > > The following basic inner join is taking too much time for me. (I’m using > count(videos.id ) instead of count(*) because my actual > query looks different,

Re: Performance issues while running select sql query

On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote: > # SELECT serorgid,appname,sum(message_count) AS mtrc0,date_trunc('day', > client_received_start_timestamp at time zone '+5:30:0')::timestamp without > time zone AS time_unit FROM analytics."test.prod.fact" WHERE >

Re: partitioning an existing table - efficient pg_dump

On Sat, Dec 30, 2017 at 09:19:05AM -0500, Robert Blayzor wrote: > On Dec 30, 2017, at 12:38 AM, Justin Pryzby <pry...@telsasoft.com> wrote: > > BTW depending on your requirements, it may be possible to make pg_dump much > > more efficient. For our data, it's reasonable t

Re: partitioning an existing table

On Fri, Dec 29, 2017 at 11:37:56PM -0500, Robert Blayzor wrote: > The docs claim that the master table “should” be empty. It it possible to > just create child tables off an existing master table with data, then just > inserting data into the new child tables. > > THe plan would be to keep data

analyze stats: child vs parent

This topic is confusing to lots of people, usually including myself, so I'm hoping to clarify it at least to myself, and maybe provide a good reference or doc update for others in the future. autovacuum/analyze automatically scans tables being inserted/updated/deleted and updates their statistics

Re: HDD vs SSD without explanation

On Sun, Jan 14, 2018 at 12:44:00PM -0800, Neto pr wrote: > Dear all > > Someone help me analyze the two execution plans below (Explain ANALYZE > used), is the query 9 of TPC-H benchmark [1]. > > I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB > 15 Krpm AND SSD Sansung

Re: HDD vs SSD without explanation

On Sun, Jan 14, 2018 at 06:25:40PM -0800, Neto pr wrote: > > The query plan is all garbled by mail , could you resend? Or post a link > > from > > https://explain.depesz.com/ On Sun, Jan 14, 2018 at 06:36:02PM -0800, Neto pr wrote: > I was not able to upload to the site, because I'm saving the

Re: postgresql 10.1 wrong plan in when using partitions bug

On Sun, Feb 04, 2018 at 05:06:38PM +0200, Mariel Cherkassky wrote: > Great, it solved the issue. Seems problematic that the planner do full > scans on all partitions in the first case isnt it ? Seems like a bug ? See also:

Re: failing to use index on UNION of matviews (Re: postgresql 10.1 wrong plan in when using partitions bug)

On Sun, Feb 04, 2018 at 11:04:56AM -0500, Rick Otten wrote: > On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane wrote: > > > Rick Otten writes: > > > I'm wrestling with a very similar problem too - except instead of > > official > > > partitions I have a

Re: Table performance with millions of rows (partitioning)

On Wed, Dec 27, 2017 at 07:54:23PM -0500, Robert Blayzor wrote: > Question on large tables… > > When should one consider table partitioning vs. just stuffing 10 million rows > into one table? IMO, whenever constraint exclusion, DROP vs DELETE, or seq scan on individual children justify the

Re: tcp_keepalives

Hi, Sorry for my poor Portugese; I didn't translate back since I cannot verify its accuracy. On Wed, Jul 04, 2018 at 10:19:41AM -0300, José Mello Júnior wrote: > Pessoal, estou em um cliente que tem um servidor Linux (não sei qual > distribuição) mas de um tempo para cá está aparecendo uma

Re: performance statistics monitoring without spamming logs

On Tue, Jul 10, 2018 at 01:54:12PM -0400, Patrick Hemmer wrote: > I'm looking for a way of gathering performance stats in a more usable > way than turning on `log_statement_stats` (or other related modules). > The problem I have with the log_*_stats family of modules is that they > log every

Re: where can I download the binaries of plpython extension

On Sun, Jul 08, 2018 at 04:06:50PM +0300, Mariel Cherkassky wrote: > Hi, > I installed postgresql v9.6/10 in our company. Which version did you install and how ? Compiled or binaries from some repo ? Using PGDG repo or some other one ? > When I try to install the extension with yum it

Re: where can I download the binaries of plpython extension

On Sun, Jul 08, 2018 at 04:24:10PM +0300, Mariel Cherkassky wrote: > I download the source files from the official website compiled them and > installed postgresql manually. > In what repository does the postgresql10-plpython exist ? or even the 9 > version ? I dont find them via yum search. If

Re: where can I download the binaries of plpython extension

On Sun, Jul 08, 2018 at 04:38:21PM +0300, Mariel Cherkassky wrote: > When installing the postgresql10-plpython one of its dependencies is > the postgresql10-server. However, I dont want to install the server but as > you can see it is a must. What can I do ? All it does is install files allowing

Re: where can I download the binaries of plpython extension

On Sun, Jul 08, 2018 at 05:36:06PM +0300, Mariel Cherkassky wrote: > I still got the binaries of the installation and I found that I have the > next directory : postgresql-10.4/src/pl/ > cd postgresql-10.4/src/pl/plpython > -rw-r--r-- 1 postgres postgres 653 May 7 23:51 Makefile > drwxr-xr-x 3

Re: where can I download the binaries of plpython extension

On Sun, Jul 08, 2018 at 04:46:47PM +0300, Mariel Cherkassky wrote: > As I mentioned earlier, I already have a running postgresql instance on the > machibe but on different pathes. I didnt want to install another one with > the default pathes because I didnt want people to think that the default >

Re: Gained %20 performance after disabling bitmapscan

On Fri, Oct 19, 2018 at 07:19:12AM +, Yavuz Selim Sertoglu wrote: > 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 use bitmapscan. So I decided disable

Re: DELETE / UPDATE from partition not optimized (11.0)

On Thu, Oct 25, 2018 at 10:43:10AM -0600, Dave E Martin wrote: > If SELECT is confident enough to limit itself to one partition, why isn't > DELETE (or UPDATE)? Because of this limitation: https://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITION-PRUNING |Currently,

Re: DELETE / UPDATE from partition not optimized (11.0)

On Fri, Oct 26, 2018 at 10:45:40AM -0500, Justin Pryzby wrote: > On Thu, Oct 25, 2018 at 10:43:10AM -0600, Dave E Martin wrote: > > If SELECT is confident enough to limit itself to one partition, why isn't > > DELETE (or UPDATE)? > > Because of this limitation: > >

Re: Fwd: Query with high planning time compared to execution time

On Mon, Nov 05, 2018 at 03:04:29PM +0800, Richard Lee wrote: > Executed perf-record and perf-report: > -bash-4.2$ perf record -g -- psql -U sri sri < > /var/lib/pgsql/10/data/pg_log/1-b10/query.txt > < ... snipped ... > That's showing perf output for the psql client. What you want is output for

Re: checkpoint occurs very often when vacuum full running

Hi, Please don't cross post to multiple lists. On Thu, Nov 15, 2018 at 08:53:14PM +0200, Mariel Cherkassky wrote: > Can someone explain the logic behind it ? I know that vacuum full isnt > something recommended but I found out that whenever I run vacuum full on my > database checkpoint occurs

Re: Fwd: Query with high planning time compared to execution time

On Mon, Nov 05, 2018 at 11:36:46AM +0800, Richard Lee wrote: > Hi, > > Debug symbols can only be enabled during configure? How about when > Postgresql is running? If you're running from RPMs (maybe from yum.postgresql.org), you can install postgresql10-debuginfo (maybe using: "debuginfo-install

Re: Partial index plan/cardinality costing

Please don't cross-post to lists. >insert into s(status, action_at, m_fk) >select > ( CASE WHEN series.n % 100 < 80 THEN > (ARRAY['E', 'C'])[(series.n % 2) + 1] >ELSE > (ARRAY['P', 'PD', 'A'])[((random() * 3)::integer % 3) + 1] >END > ), > ( >CASE WHEN series.n % 100 < 80

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

On Mon, Sep 03, 2018 at 11:17:58AM +0300, Mariel Cherkassky wrote: > Hi, > I already checked and on all the tables that uses the id col of the main > table as a foreign key have index on that column. > > So, it seems that the second solution is the fastest one. It there a reason > why the delete

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

On Mon, Sep 03, 2018 at 09:27:52AM +0300, Mariel Cherkassky wrote: > I'm trying to find the best way to delete most of the table but not all of it > according to a range of dates. > Indexes: > "end_date_idx" btree (end_date) > Referenced by: > TABLE "table1" CONSTRAINT

Re: Why could different data in a table be processed with different performance?

Sorry, dropped -performance. Has the table been reindexed (or pg_repack'ed) since loading (or vacuumed for that matter) ? >>> Not sure what you mean... We created indexes on some fields (on >> I mean REINDEX INDEX articles_pkey; >> Or (from "contrib"): /usr/pgsql-10/bin/pg_repack -i

Re: Why could different data in a table be processed with different performance?

On Thu, Sep 20, 2018 at 05:07:21PM -0700, Vladimir Ryabtsev wrote: > I am experiencing a strange performance problem when accessing JSONB > content by primary key. > I noticed that with some IDs it works pretty fast while with other it is > 4-5 times slower. It is suitable to note, there are two

Re: Why could different data in a table be processed with different performance?

On Mon, Sep 24, 2018 at 03:28:15PM -0700, Vladimir Ryabtsev wrote: > > it is not unusual to have 1GB cache or more... and do not forget to drop > the cache between tests + do a sync > I also reviewed import scripts and found the import was done in DESCENDING > order of IDs. This seems

Re: Why could different data in a table be processed with different performance?

On Mon, Sep 24, 2018 at 05:59:12PM -0700, Vladimir Ryabtsev wrote: > > This seems significant..it means the heap was probably written in > backwards > order relative to the IDs, and the OS readahead is ineffective when index > scanning across a range of IDs. > But again, why is it different for

reference regarding write load during different stages of checkpoint

I'm hoping to find a document I once read about the write load. As best I can recall, it looked something like this: . at beginning of (spread) checkpoint, larger than average write load to pg_wal/, due to full_page_writes; . during most of checkpoint, decreasing WAL due to FPW, . towards end

Re: link to Slow_Query_Questions from wiki/Main Page

I asked few weeks ago [0] but didn't get a response on -docs so resending here for wider review/discussion/. [0] https://www.postgresql.org/message-id/flat/20180908012957.GA15350%40telsasoft.com On Fri, Sep 07, 2018 at 08:29:57PM -0500, Justin Pryzby wrote: > Hi, > > I'm suggestin

Re: Explain is slow with tables having many columns

On Mon, Sep 24, 2018 at 12:22:28PM -0700, legrand legrand wrote: > Hello, > I have found that explain on tables with many (hundreds) columns > are slow compare to nominal executions. See also this thread from last month:

Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes

On Thu, Sep 27, 2018 at 01:08:05PM -0400, Arjun Ranade wrote: > When I look at the EXPLAIN ANALYZE output, it seems that it's using a > drastically different query plan for the INSERT+SELECT than SELECT by > itself. The fast, SELECT plan is using parallel query, which isn't available for

Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes

On Thu, Sep 27, 2018 at 03:37:57PM -0400, Arjun Ranade wrote: > Yes, that join is concerning (red text below). The conditions all need to > be checked so they are independent. You can play with the join conditions to see which test is getting such a bad estimate, or if it's a combination of

Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes

On Thu, Sep 27, 2018 at 04:50:36PM -0700, Vladimir Ryabtsev wrote: > Additionally, I think author can try CREATE STATISTICS on the bunch of > columns used in join. Very low rows estimate for this join may come from > multiplying selectivities for each column assuming they are independent. MV

Re: PostgreSQL Read IOPS limit per connection

On Thu, Dec 27, 2018 at 02:44:55PM -0200, Haroldo Kerry wrote: > PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Debian 9.6.10-1.pgdg80+1), > Connected to SAN: Dell Compellent SC2020, with 7 x Samsung PM1633 SSDs > https://www.samsung.com/us/labs/pdfs/collateral/pm1633-prodoverview-2015.pdf, >

Re: select query does not pick up the right index

On Fri, Jan 04, 2019 at 08:58:57AM +, Abadie Lana wrote: > SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, > attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, > array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='...' AND >

Re: autovacuum doesnt run on the pg_toast_id table

On Thu, Jan 17, 2019 at 07:28:52PM +0200, Mariel Cherkassky wrote: ... > Now, I realized from the autovacuum > logging, that when autovacuum runs on the original table (A) it doesn't > necessary run on the toasted table and this is very weird. ... > Any idea why the autovacuum doesnt vacuum both

Re: Very long query planning times for database with lots of partitions

On Tue, Jan 22, 2019 at 02:44:29PM +0100, Mickael van der Beek wrote: > Hey everyone, > > I have a PostgreSQL 10 database that contains two tables which both have > two levels of partitioning (by list and using a single value). Meaning that > a partitioned table gets repartitioned again. > > The

Re: Database size 1T but unclear why

On Sun, Dec 09, 2018 at 10:01:08AM -0600, Justin Pryzby wrote: > On Sun, Dec 09, 2018 at 05:18:55PM +0200, Mariel Cherkassky wrote: > > I'm trying to understand why my database consume so much space. I checked > > the space it consume on disk : To find single relations which are

Re: Database size 1T but unclear why

On Sun, Dec 09, 2018 at 05:18:55PM +0200, Mariel Cherkassky wrote: > I'm trying to understand why my database consume so much space. I checked > the space it consume on disk : This seems to be essentially the same question you asked last month, so should either continue the existing thread or

Re: dsa_allocate() faliure

On Fri, Nov 23, 2018 at 03:31:41PM +0100, Jakub Glapa wrote: > Hi Justin, I've upgrade to 10.6 but the error still shows up: > > If I set it to max_parallel_workers=0 I also get and my connection is being > closed (but the server is alive): > > psql db@host as user => set max_parallel_workers=0;

Re: explain analyze faster then query

On Sun, Nov 25, 2018 at 03:37:46PM +0200, Mariel Cherkassky wrote: > I run it from inside the machine on the local database. > For example : > > db=# create table rule_test as select generate_series(1,1); > SELECT 1 > db=# explain analyze select generate_series from rule_test

Re: explain analyze faster then query

Cc: pgsql-performance@lists.postgresql.org, pgsql-ad...@lists.postgresql.org Please avoid simultaneously sending the same question to multiple lists. It means that people can't see each others replies and everything that implies. On Sun, Nov 25, 2018 at 03:08:33PM +0200, Mariel

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

On Tue, Nov 27, 2018 at 06:30:04PM -0200, Sanyo Moura wrote: >>> 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. > > Below is the script that creates one

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: >>> 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. I was able to reproduce this behavior. For my version

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

On Tue, Nov 27, 2018 at 06:44:02PM -0600, Justin Pryzby wrote: > On Tue, Nov 27, 2018 at 9:17 AM Sanyo Moura wrote: > >>> I'm running performance tests for my application at version 11.1 and > >>> encountered queries with high planning time compared to the same planning

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

On Tue, Nov 27, 2018 at 11:36:09PM -0200, Sanyo Moura wrote: > However, in the test I did in version 11.0, "Precio" is partitioned into > only 21 partitions. So it really is a problem introduced in version 11, and > it has to do with a large number of partitions in a table. Thanks for confirming.

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

On Wed, Nov 28, 2018 at 05:03:15PM +1300, David Rowley wrote: > Does it still take that long after running ANALYZE on the partitioned table? Yes ; I've just reproduced the problem with a variation on Sanyo's query, retrofitted onto the empty "partbench" table you used for testing in July:

Re: dsa_allocate() faliure

On Wed, Nov 21, 2018 at 03:26:42PM +0100, Jakub Glapa wrote: > Looks like my email didn't match the right thread: > https://www.postgresql.org/message-id/flat/CAMAYy4%2Bw3NTBM5JLWFi8twhWK4%3Dk_5L4nV5%2BbYDSPu8r4b97Zg%40mail.gmail.com > Any chance to get some feedback on this? In the related

Re: Performance impact of updating target columns with unchanged values ON CONFLICT

On Thu, Nov 22, 2018 at 01:31:10PM -0800, Abi Noda wrote: > In other words, is Postgres smart enough to not actually write to disk any > columns that haven’t changed value or update indexes based on those columns? You're asking about what's referred to as Heap only tuples:

Re: dsa_allocate() faliure

Hi, thanks for following through. On Mon, Nov 26, 2018 at 04:38:35PM +0100, Jakub Glapa wrote: > I had a look at dmesg and indeed I see something like: > > postgres[30667]: segfault at 0 ip 557834264b16 sp 7ffc2ce1e030 > error 4 in postgres[557833db7000+6d5000] That's useful, I think

Re: Slow Bitmap Index Scan

On Wed, Nov 28, 2018 at 07:08:53PM +, Scott Rankin wrote: > We recently moved our production database systems from a 9.4 running on a > self-managed EC2 instance to 9.6.10 on Amazon’s AWS (same RAM, CPU). After > the move, we’re finding that certain queries that we run against a GIN >

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

On Wed, Nov 28, 2018 at 05:03:15PM +1300, David Rowley wrote: > On Wed, 28 Nov 2018 at 03:16, Sanyo Moura wrote: > > 11.0 > > Planning Time: 7.238 ms > > Planning Time: 2.638 ms > > > > 11.5 > > Planning Time: 15138.533 ms > > Execution Time: 2.310 ms > > Does it still take that long after

Re: select query does not pick up the right index

On Mon, Jan 07, 2019 at 04:09:50PM +, Abadie Lana wrote: > "channel_pkey" PRIMARY KEY, btree (channel_id) > "unique_chname" UNIQUE CONSTRAINT, btree (name) > "channel_name_channel_id_idx" btree (name, channel_id) Note, the third index is more or less redundant. > I would say

Re: Query Performance Issue

On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote: > Have this explain analyze output : > > *https://explain.depesz.com/s/Pra8a * Row counts are being badly underestimated leading to nested loop joins: |Index Scan using

Re: Query Performance Issue

On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote: > Have this explain analyze output : > > *https://explain.depesz.com/s/Pra8a <https://explain.depesz.com/s/Pra8a>* On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote: > On Sat, 29 Dec 2018 at 04:32, Ju

Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours

y-scan, but maybe still making many accesses to the heap (table) for pages which aren't all-visible. You can maybe improve by vacuuming (perhaps by daily cronjob or by ALTER TABLE SET autovacuum threshold or scale factor). -- Justin Pryzby System Administrator Telsasoft +1-952-707-8581

Re: Increasing parallelism of queries while using file fdw and partitions

On Tue, Dec 18, 2018 at 08:39:36PM -0800, Patrick Mulrooney wrote: > Wondering if anyone had any thoughts on how to tweak my setup to get it to > read many files at once instead of one at a time when using file fdw and > partitions. I found this:

Re: Q on SQL Performance tuning

On Sun, Jan 27, 2019 at 08:43:15AM +, Bhupathi, Kaushik (CORP) wrote: > 2) Is there anyway to know the historical execution plan details of a > particular SQL ? Per my understanding so far since there is no concept of > shared pool unlike Oracle every execution demands a new hard parse.

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

On Sun, Jan 27, 2019 at 01:09:16PM +0530, Saurabh Nanda wrote: > It seems that PGOPTIONS="-c synchronous_commit=off" has a significant > impact. However, I still can not understand why the TPS for the optimised > case is LOWER than the default for higher concurrency levels! Do you know which of

Re: EXPLAIN PLAN for DELETE CASCADE or DELETE not using pkey index despite EXPLAINing that it would?

On Thu, Mar 21, 2019 at 03:31:42PM -0400, Gunther wrote: > Hi, > > I have 250 rows to delete, but they are a target to a bunch of child tables > with foreign key on delete cascade. > > EXPLAIN DELETE FROM Foo WHERE id = (SELECT fooId FROM Garbage); Probably because:

Re: RAM usage of PostgreSql

Hi, |Cc: pgsql-nov...@postgresql.org, pgsql-performa...@postgresql.org Please don't cross post to multiple lists. On Thu, Apr 04, 2019 at 08:18:01PM +0530, Prasad wrote: > There are lot of allocations in postgresql.conf file, for example > shared_buffers, work_mem...etc. > > As per my

Re: Planning performance problem (67626.278ms)

On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote: > po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz > napsal: > > > We have some very strange query planning problem. Long story short it > > takes 67626.278ms just to plan. Query execution takes 12ms. > > > > Query has 7 joins

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

Some ideas: You could ALTER TABLE SET (fillfactor=50) to try to maximize use of HOT indices during UPDATEs (check pg_stat_user_indexes). You could also ALTER TABLE SET autovacuum parameters for more aggressive vacuuming. You could recreate indices using the CONCURRENTLY trick (CREATE INDEX

Re: Query slow for new participants

On Tue, Feb 26, 2019 at 12:22:39AM +0100, supp...@mekong.be wrote: > Hardware > Standard DS15 v2 (20 vcpus, 140 GB memory) > "effective_cache_size" "105GB" "configuration file" > "effective_io_concurrency" "200" "configuration file" > "maintenance_work_mem" "2GB" "configuration file" >

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

On Sun, Feb 24, 2019 at 10:06:10PM -0500, Gunther wrote: > The index isn't required at all if all my pending jobs are in a partition of > only pending jobs. In that case the plan can just be a sequential scan. .. > because an index really isn't required. The actual pending partition will > always

Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum

On Wed, Feb 27, 2019 at 09:47:13AM -0500, Fd Habash wrote: > I have been able to locate four google search results with the same inquiry. > What’ve been able to understand is … > > 1. If auto-vaccum is working as expected, stats collector does not nullify > these values as part of a startup

Re: How to get the content of Bind variables

On Fri, Mar 01, 2019 at 06:47:06PM +, ROS Didier wrote: > log_line_prefix = '%t [%p]: [%l-1] [%x] user=%u,db=%d,client=%h' On Sat, Mar 02, 2019 at 01:14:44PM +, ROS Didier wrote: > 2019-03-01 14:53:37 CET [24803]: [129-1] [3686] > user=pgbd_preint_sg2,db=pgbd_preint_sg2 LOG: process

Re: neither CPU nor IO bound, but throttled performance

On Wed, Feb 20, 2019 at 06:32:49PM -0500, Gunther wrote: > Hi, I have an Amazon Linux based Postgresql 11 server here on a t2.medium > EC2 instance. > > Everything flows quite nicely. Except, I don't understand why I can't max > out the CPU or the IO, instead, IO is almost negligible yet the CPU

Re: autovacuum just stop vacuuming specific table for 7 hours

On Wed, Mar 06, 2019 at 06:47:21PM +0200, Mariel Cherkassky wrote: > Those settings helped but the table still grey very much. I wrote a script > that monitored some metadata about the table (pg_stat_all_tables,count(*) > from orig and toasted table). I let the system monitor the table for a week

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

On Sun, Feb 24, 2019 at 04:34:34PM -0500, Corey Huinker wrote: > I think your solution may be something like this: > 1. Create a new table, same columns, partitioned on the pending column. > 2. Rename your existing queue table old_queue to the partitioned table as a > default partition. > 3.

Re: Aggregate and many LEFT JOIN

On Fri, Feb 22, 2019 at 04:14:05PM +0100, kimaidou wrote: > Explain shows that the GROUP AGGREGATE and needed sort kill the performance. > Do you have any hint how to optimize this ? > https://explain.depesz.com/s/6nf This is writing 2GB tempfile, perhaps the query would benefit from larger

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

On Sun, Feb 24, 2019 at 12:45:34PM -0500, Gunther wrote: > What I am most puzzled by is that no matter how long I wait, the DROP INDEX > CONCURRENTLY never completes. Why is that? https://www.postgresql.org/docs/11/sql-dropindex.html CONCURRENTLY [...] With this option, the command instead waits

Re: Query slow for new participants

On Mon, Feb 25, 2019 at 03:41:18AM -0700, Kim wrote: > Is there any way how I can make the queries fast for new participants? This > is a big problem, because for new participants, speed is even more > important. > > Thank you for your help. Could you include information requested here ?

Re: impact of auto explain on overall performance

On Thu, Mar 14, 2019 at 07:29:17AM +, Stephan Schmidt wrote: > 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, but

Re: Distributing data over "spindles" even on AWS EBS, (followup to the work queue saga)

On Wed, Mar 13, 2019 at 02:44:10PM -0400, Gunther wrote: > You see that I already did a lot to balance IO out to many different > tablespaces that's why there are so many volumes. I wonder if it wouldn't be both better and much easier to have just 1 or 2 tablespaces and combine drives into a

Re: Shared_buffers

On Tue, Mar 12, 2019 at 04:03:11PM -0400, MichaelDBA wrote: > Set shared_buffers more accurately by using pg_buffercache extension and the > related queries during high load times. I've tuned ~40 postgres instances, primarily using log_checkpoints and pg_stat_bgwriter, with custom RRD graphs.

Re: Partitioning Optimizer Questions and Issues

On Fri, Feb 08, 2019 at 11:13:51AM +, keith anderson wrote: > So to summarise the findings/questions from above: > - It seems like the Postgres optimizer sometimes uses the partition level > statistics, and sometimes the global table level statistics? Or is it using > something else?- With

Re: dsa_allocate() faliure

Hi, On Mon, Nov 26, 2018 at 09:52:07AM -0600, Justin Pryzby wrote: > Hi, thanks for following through. > > On Mon, Nov 26, 2018 at 04:38:35PM +0100, Jakub Glapa wrote: > > I had a look at dmesg and indeed I see something like: > > > > postgres[30667]: segfault

Re: slow to run query 5000 times

Hi, Please don't send images to the list, you can send a link to one of the image host websites if you need to describe something graphical. But here, you could just send the queries and \d for the tables. On Sat, Feb 09, 2019 at 01:45:50PM -0300, Evandro Abreu wrote: > I have a report that

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

On Thu, Feb 14, 2019 at 09:38:52AM +, suganthi Sekar wrote: > i am using Postgresql 11, i have 2 partition table , when i joined both > table in query > a table its goes exact partition table , but other table scan all partition > > please clarify on this . > > Example : > > explain

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

On Thu, Feb 14, 2019 at 10:38:36AM +, suganthi Sekar wrote: > u mean the below parameter need to set on . its already on only. > alter system set constraint_exclusion to 'on'; No, I said: > You can work around it by specifying the same condition on > b.call_created_date: > > AND

Re: partition pruning

On Thu, Feb 14, 2019 at 01:37:49PM +0100, Laurenz Albe wrote: > There is no condition on the table "call_report2" in your query, > so it is not surprising that all partitions are scanned, right? Some people find it surprising, since: a.call_id=b.call_id suganthi Sekar wrote: > > explain analyze

Re: JIT overhead slowdown

Hi, On Fri, Jan 18, 2019 at 02:12:23PM +, Luis Carril wrote: > we noticed that in the presence of a schema with many partitions the > jitting overhead penalizes the total query execution time so much that the > planner should have decided not to jit at all. For example without jitting

Re: Why isn't an index scan being used?

On Tue, Feb 19, 2019 at 05:10:43PM -0700, Abi Noda wrote: > I have a table as defined below. The table contains 1,027,616 rows, 50,349 > of which have state='open' and closed IS NULL. Since closed IS NULL for all > rows where state='open', I want to remove the unnecessary state column. > > CREATE

Re: Why isn't an index scan being used?

On Tue, Feb 19, 2019 at 09:29:46PM -0700, Michael Lewis wrote: > On Tue, Feb 19, 2019, 8:00 PM Andrew Gierth > wrote: > > > > "Abi" == Abi Noda writes: > > Abi> However, when I index the closed column, a bitmap scan is used > > Abi> instead of an index scan, with slightly slower

Re: dsa_allocate() faliure

On Mon, Feb 04, 2019 at 08:31:47PM +, Arne Roland wrote: > I could take a backup and restore the relevant tables on a throwaway system. > You are just suggesting to replace line 728 > elog(FATAL, > "dsa_allocate could not find %zu free > pages", npages); > by

Re: Commit(?) overhead

On Wed, Apr 10, 2019 at 09:26:22AM +1200, Duncan Kinnear wrote: > - On 5 Apr, 2019, at 4:14 AM, Jeff Janes wrote: > > > 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

  1   2   3   4   >