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 anal

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 take

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 pa

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: dsa_allocate() faliure

70 children that inherit from it. The children > and the corresponding indexes should be named like '%part%'. > > I attached a query with a plan that fails on my test database. Thanks - note that previously Thomas said: On Mon, Dec 03, 20

Re: upgrade from 9.6 to 10/11

On Mon, Jan 28, 2019 at 11:20:28AM +0100, Laurenz Albe wrote: > Mariel Cherkassky wrote: > > I'm planning our db upgrade from 9.6. Basically I wanted to check how stable > > is pg11 version. I'm considering upgrading from 9.6 to 10 and then to 11 > > immediatly. > > Is there a way to upgrade direc

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 th

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

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 that

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: select query does not pick up the right index

On Thu, Jan 03, 2019 at 12:57:27PM +, Abadie Lana wrote: > Main parameters : effective_cache_size : 4GB, shared_buffers 4GB, work_mem 4MB I doubt it will help much, but you should consider increasing work_mem, unless you have many expensive queries running at once. Could you also send the res

Re: select query does not pick up the right index

: 1 >Workers Launched: 1 >Single Copy: true Do you have force_parallel_mode set ? http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html -- Justin Pryzby System Administrator Telsasoft +1-952-707-8581

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

Re: PostgreSQL Read IOPS limit per connection

On Thu, Dec 27, 2018 at 08:20:23PM -0500, Jeff Janes wrote: > Also, you would want to use the newest version of PostgreSQL, as 9.6 > doesn't have parallel query, which is much more generally applicable than > effective_io_concurrency is. It *does* have parallel query (early, somewhat limited suppo

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, > RAID10+RA

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: https://www.postgresql.org/docs/current/parallel-s

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

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 link

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 runnin

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

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

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

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

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

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 orde

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 Cherkassk

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: 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: https://git.postgresql.o

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 thread

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 dur

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 t

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 p

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

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 bitma

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: psql: fe_sendauth: no password supplied

It's a question better directed to the -general list. You'll either want to specify the DB name and DB user in check_postgres invocation, or otherwise add an entry to pg_hba.conf, like: hostts nagios 192.168.122.1/32trust https://bucardo.org/check_postgres/check_postgres

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 stati

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 tests

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 INSERT+S

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 su

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 one

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: > > I *suspect* VACUUM FULL won't help, since (AIUI) it copies all "visible" ... > I am going copy the slow range into a table nearby and see if it reproduces > (I hope "INSERT INTO t2 SELECT * FROM t1 WHERE ..." will keep existing

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 significant

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: https://www.postgresql.org/message-id/flat/CAEe%3DmRnNNL3RDKJDmY%3D_mpcpAb5u

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 arti

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 m

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

index-only-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: 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 c

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

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 single

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 p

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

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

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 download

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 mensa

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

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: Possible optimisation: push down SORT and LIMIT nodes

On Wed, May 30, 2018 at 02:02:31PM -0500, Justin Pryzby wrote: > For diagnostic purposes, I was able to to vastly improve the query runtime > with > a CTE (WITH): I realized this was broken as soon as I sent it (for the essential reason of discarding rows before having sorted them).

Re: Possible optimisation: push down SORT and LIMIT nodes

On Wed, May 30, 2018 at 03:46:40PM +, Christopher Wilson wrote: > We have a query which is rather slow (about 10 seconds), and it looks like > this: > > The inventory table has the quantity of each asset in the inventory on each > date (complete SQL to create and populate the tables with dumm

Re: propose web form for submission of performance problems

On Thu, May 24, 2018 at 06:27:31PM -0700, Craig James wrote: > On Thu, May 24, 2018 at 4:57 PM, Justin Pryzby wrote: > > > What would the list think of a web form for submitting problems the > > performance > > list, similar to the pgsql-bugs form? > > > > Alt

propose web form for submission of performance problems

What would the list think of a web form for submitting problems the performance list, similar to the pgsql-bugs form? Alternately, or perhaps additionally, a script (hopefully bundled with postgres) which collects at least the non-query specific info and probably creates .logfile file for attachme

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

On Wed, May 23, 2018 at 10:20:42PM -0700, pavan95 wrote: > Hi Justin, > > Please find the below explain plan link. > > Link: https://explain.depesz.com/s/owE That's explain analyze but explain(analyze,buffers) is better. Is this on a completely different server than the previous plans ? Th

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

here's handful of questions: On Wed, May 23, 2018 at 08:43:22AM -0500, Justin Pryzby wrote: > - postgres parameters: what are shared_buffers, work_mem, > effective_cache_size ? >+ https://wiki.postgresql.org/wiki/Server_Configuration > - are there other DBs/applications r

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

On Wed, May 23, 2018 at 12:01:06AM -0700, pavan95 wrote: > As said, created index on the res_users.res_employee_id and the below link > is the explain plan result. > > Link: https://explain.depesz.com/s/hoct > > And the cost of Previous query is 92,129 and the cost of current modified > query af

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 try

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:32:59AM -0700, pavan95 wrote: > Sample query plan: > Limit (cost=92129.35..92129.63 rows=10 width=248) Would you send the output of explain(analyze,buffers) for the whole query ? And/or paste it into explain.depesz site and send a link. Justin

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 w

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

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

Re: Performance issues while running select sql query

On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote: > Hi, > > I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance type > with 500 GB volume of volume type io1 with 25000 IOPS and I am seeing > performance issues. The sql query response takes around *127713.413 ms *t

Re: Slow query on partitioned table.

Re-added -performance. On Tue, Mar 27, 2018 at 05:13:25PM +0100, Glenn Pierce wrote: > Damn as I was playing with the indexes I must have deleted the constraints :( > Question if I have a constraint like > > ALTER TABLE sensor_values_2007q1 > ADD CONSTRAINT sensor_values_2007q1_sensor_id_timest

Re: Slow query on partitioned table.

On Tue, Mar 27, 2018 at 03:14:30PM +0100, Glenn Pierce wrote: > Hi I am having terrible trouble with a simple partitioned table. > Select queries are very slow. > The child tables are all like > Check constraints: > "sensor_values_2018q1_timestamp_check" CHECK (ts >= '2018-01-01 > 00:00:00

Re: effective_io_concurrency on EBS/gp2

On Wed, Jan 31, 2018 at 04:34:18PM -0300, Claudio Freire wrote: > In my experience playing with prefetch, e_i_c>0 interferes with kernel > read-ahead. What you've got there would make sense if what postgres > thinks will be random I/O ends up being sequential. With e_i_c=0, the > kernel will optimi

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 views on top of a bunch (50+) of unioned material

Re: postgresql 10.1 wrong plan in when using partitions bug

On Sun, Feb 04, 2018 at 05:28:52PM +0200, Mariel Cherkassky wrote: > I read those two links and I dont think that they are relevant because : 1 > 1)I didnt do any join. > 2)I used a where clause in my select https://www.postgresql.org/docs/current/static/ddl-partitioning.html |The following caveat

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: https://www.postgresql.org/message-id/20170725131650.GA30519%40telsasoft.co

Re: Query Slow After 2018

On Mon, Jan 29, 2018 at 12:32:59AM +0700, Nur Agus wrote: > The following query run in just 9 ms: > "distrib_reports"."month" = 1 AND > "distrib_reports"."year" = 2017 AND > "distrib_reports"."state" = 'SUBMITTED' AND > "distrib_report_groups"."distrib_report_group_type_id" = > '559a5fdc-418d-

Re: Bad plan

On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote: > Hello all, > > So I have a view, for which I can select all rows in about 3s (returns ~80k > rows), but if I add a where clause on a column, it takes +300s to return > the ~8k lines. > > From the plan, I see that it expects to re

Re: HDD vs SSD without explanation

uot;dd" without the DB or anything else using the drive. That gets peformance of the drive, without the DB. You should probably rerun the "dd" command using /dev/sdb1 if there's an partition table on top (??). I'm still wondering about these: On Sun, Jan 14, 2018 at 09

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 e

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 EV

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 wrote: > > BTW depending on your requirements, it may be possible to make pg_dump much > > more efficient. For our data, it's reasonable to assume that a table i

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

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 minor

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: Autoanalyze CPU usage

On Tue, Dec 19, 2017 at 08:47:52AM -0800, Habib Nahas wrote: > We operate an RDS postgres 9.5 instance and have periodic CPU spikes to > 100%. These spikes appear to be due to autoanalyze kicking on our larger > tables. Not sure if it'll help you, but for our large, insert-only tables partitioned

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

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

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

On Tue, Dec 12, 2017 at 01:29:48AM -0800, Jeff Janes wrote: > 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: > > > In any case, given that we do this calculation without regard > > > to any specific

Re: CPU 100% usage caused by unknown postgres process..

On Wed, Dec 13, 2017 at 11:36:11AM +0100, Laurenz Albe wrote: > Dinesh Chandra 12108 wrote: > > My CPU utilization is going to 100% in PostgreSQL because of one unknown > > process /x330341 is running from postgres user. > > > > PID USER PR NIVIRTRESSHR S %CPU %MEM TIME

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 > >>> broken to agree with the physical order. That is wrong, right?

<    1   2   3   4   5   >