Re: Too few rows expected by Planner on partitioned tables

On Tue, Jul 21, 2020 at 01:09:22PM +, Julian Wolf wrote: > Our problem is, that the planner always predicts one row to be returned, > although only a part of the primary key is queried. This problem exceeds > feasibility of performance rapidly - a query only involving a few days > already ta

Re: Too few rows expected by Planner on partitioned tables

On Wed, Jul 22, 2020 at 06:33:17AM +, Julian Wolf wrote: > Hello Justin, > > > thank you very much for your fast response. > > > Is there a correlation between daterange and spacial_feature_id ? > > I am not entirely sure, what you mean by that. Basically, no, they are not > correlated - s

Re: Too few rows expected by Planner on partitioned tables

On Tue, Jul 21, 2020 at 01:09:22PM +, Julian Wolf wrote: > Hello, > > A description of what you are trying to achieve and what results you expect: > Our database is growing on a daily basis by about 2.5million rows per table > (2 at the moment). Because of that, we decided to partition the da

Re: Query takes way longer with LIMIT, and EXPLAIN takes way longer than actual query

On Fri, Aug 14, 2020 at 02:34:52PM -0700, Ken Tanzer wrote: > Hi. I've got a query that runs fine (~50ms). When I add a "LIMIT 25" to > it though, it takes way longer. The query itself then takes about 4.5 > seconds. And when I do an explain, it takes 90+ seconds for the same query! Due to the

Re: Query takes way longer with LIMIT, and EXPLAIN takes way longer than actual query

On Fri, Aug 14, 2020 at 03:40:40PM -0700, Ken Tanzer wrote: > On Fri, Aug 14, 2020 at 3:04 PM Justin Pryzby wrote: > > Due to the over-estimated rowcount, the planner believes that (more) rows > > will be output (sooner) than they actually are: > > > >-> Nest

Re: Query Performance in bundled requests

On Tue, Sep 08, 2020 at 10:30:50AM +, Dirk Krautschick wrote: > Update: Better title and format corrections > > Hi %, > > in order to be able to readjust the effects of the stored procedure and, if > necessary, to save turnaround times, different requests can be concatenated > using semicol

Re: autoanalyze creates bad plan, manual analyze fixes it?

On Mon, Sep 14, 2020 at 07:11:12PM -0400, Robert Treat wrote: > Howdy folks, > > Recently i've run into a problem where autoanalyze is causing a query > plan to flip over to using an index which is about 10x slower, and the > problem is fixed by running an alayze manually. some relevant info: I t

Re: Single column vs composite partial index

On Tue, Sep 15, 2020 at 10:33:24PM +, Nagaraj Raj wrote: > Hi, > I'm running one query, and I created two types of index one is composite and > the other one with single column one and query planner showing almost the > same cost for both index bitmap scan, I'm not sure which is appropriate t

Re: Query performance

On Thu, Oct 22, 2020 at 12:32:29AM +, Nagaraj Raj wrote: > Hi, I have long running query which running for long time and its planner > always performing sequnce scan the table2.My gole is to reduce Read IO on the > disk cause, this query runns more oftenly ( using this in funtion for ETL).  >

Re: Query Performance / Planner estimate off

On Wed, Oct 21, 2020 at 04:42:02PM +0200, Mats Olsen wrote: > On 10/21/20 2:38 PM, Sebastian Dressler wrote: > > > On 20. Oct 2020, at 11:37, Mats Julian Olsen > > > > > [...] > > > > > > 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR > > > > > >

Re: Query Performance / Planner estimate off

On Thu, Oct 22, 2020 at 09:36:03AM +0200, Mats Olsen wrote: > On 10/22/20 8:37 AM, Justin Pryzby wrote: > > These look redundant (which doesn't matter for this the query): > > > > Partition key: RANGE (block_number) > > Indexes: > > "transactions_b

Re: Understanding bad estimate (related to FKs?)

On Mon, Oct 26, 2020 at 12:50:38PM -0400, Philip Semanchuk wrote: > I'm trying to understand a bad estimate by the planner, and what I can do > about it. The anonymized plan is here: https://explain.depesz.com/s/0MDz What postgres version ? Since 9.6(?) FKs affect estimates. > The item I'm focus

Re: Postgres Optimizer ignores information about foreign key relationship, severely misestimating number of returned rows in join

On Mon, Oct 26, 2020 at 03:58:05PM +, Ehrenreich, Sigrid wrote: > Hi Performance Guys, > > I hope you can help me. I am joining two tables, that have a foreign key > relationship. So I expect the optimizer to estimate the number of the > resulting rows to be the same as the number of the ret

Re: Understanding bad estimate (related to FKs?)

On Mon, Oct 26, 2020 at 11:20:01AM -0600, Michael Lewis wrote: > On Mon, Oct 26, 2020 at 11:14 AM Philip Semanchuk > wrote: > > > >> The item I'm focused on is node 23. The estimate is for 7 rows, actual > > is 896 (multiplied by 1062 loops). I'm confused about two things in this > > node. > > >

Re: Understanding bad estimate (related to FKs?)

On Mon, Nov 02, 2020 at 02:09:03PM -0500, Philip Semanchuk wrote: > Bien merci, yes, I've visited most of those links and learned an enormous > amount from them. I've downloaded many of them for re-reading, including > yours. :-) It's helpful to be reminded of them again. > > EXPLAIN ANALYZE tel

Re: Postgres using nested loops despite setting enable_nestloop to false

On Tue, Nov 17, 2020 at 02:47:55PM +0100, Frits Jalvingh wrote: > But lately while migrating to Postgres 13 (from 9.6) we found that Postgres > does not (always) obey the enable_nestloop = false setting anymore: some > > The execution plan on Postgres 13.1: Could you send the plans under pg13 and

Re: Postgres using nested loops despite setting enable_nestloop to false

On Tue, Nov 17, 2020 at 04:58:45PM +0100, Frits Jalvingh wrote: > Hi Justin, thanks for your help! > I have attached both plans, both made with set enable_nestloop = false in > the attachments. > On the Postgresql 13 server work_mem is 64MB. It cannot really be higher > there because Postgresql doe

Re: Simple update query is slow

On Sun, Nov 22, 2020 at 02:18:10AM +0530, Nandakumar M wrote: > Just realised that the time difference between explain analyze plan > and /timing result is due to the implicit commit. Can you run with SET client_min_messages=debug; and SET log_lock_waits=on; Oh, but your server is too old for that

Re: time taking deletion on large tables

On Thu, Dec 03, 2020 at 08:43:57PM +0530, Ravikumar Reddy wrote: > Please try the code below. Execute all the statements in one transaction. > > select * into new_table from old_table where type = 'abcz'; > truncate table old_table; > inesrt into old_table select * from new_table; This looks l

Re: Temporarily disable not null constraints

On Thu, Dec 03, 2020 at 07:58:15PM +, Nagaraj Raj wrote: > Can we disable not null constraints temporarily in the session-based > transaction, like we disable FK constraints?  If you're trying to temporarily violate the not-null constraint.. I don't know if it's a good idea.. ..but maybe thi

Re: Pg_locks and pg_stat_activity

On Fri, Dec 04, 2020 at 01:31:14PM +0530, aditya desai wrote: > Hi Justin, > Many thanks for your response. Please see my response below. > > What do you mean by API ? If it's a different client, how does it connect ? > Queries are getting called from Web UI built in Microservices spring boot. >

Re: How to deal with analyze gathering irrelevant stats

On Mon, Jan 11, 2021 at 04:50:12PM +0100, Rémi Chatenay wrote: > We are experiencing some slow queries due to the query planner using an > incorrect index. It is using an unoptimized index because the stats are > computed during the night when the data is not the same as during the day. > > CREATE

Re: Query performance issue

On Fri, Jan 22, 2021 at 01:53:26AM +, Nagaraj Raj wrote: > Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle > Postgres 11 | db<>fiddle > Server configuration is: Version: 10.11RAM - 320GBvCPU - 32  > "maintenance_work_mem" 256MB"work_mem"             1GB"shared_buffers" 64GB > A

Re: Slow query and wrong row estimates for CTE

On Mon, Feb 15, 2021 at 12:49:29PM -0500, Dane Foster wrote: > PostgreSQL version: PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc > (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit > EXPLAIN (ANALYZE, BUFFERS) > WITH max_spi AS ( Since v12, CTEs are usually inlined by default. I suspect i

Re: tables meta data collection

ll_tables as st  Left outer join > pg_catalog.pg_description pgd on (pgd.objoid=st.relid)  left outer join > information_schema.columns c on (pgd.objsubid=c.ordinal_position    and  > c.table_schema=st.schemaname and c.table_name=st.relname)where > c.table_name='test'o

Re: Fwd: different execution time for the same query (and same DB status)

On Sat, Mar 06, 2021 at 10:40:00PM +0100, Francesco De Angelis wrote: > The problem is the following: the query can take between 20 seconds and 4 > minutes to complete. Most of times, when I run the query for the first time > after the server initialisation, it takes 20 seconds; but if I re-run it

Re: wide table, many many partitions, poor query performance

On Mon, Mar 15, 2021 at 10:53:06AM -0600, S Bob wrote: > We have a client that is running PostgreSQL 12, they have a table with 212 > columns and 723 partitions > > It seems the planning time is consumed by generating 723 sub plans Is plannning time the issue ? Please show diagnostic output. You

Re: Odd (slow) plan choice with min/max

On Tue, Mar 23, 2021 at 03:00:38PM +1100, Paul McGarry wrote: > I have a query where Postgresql (11.9 at the moment) is making an odd plan > choice, choosing to use index scans which require filtering out millions of > rows, rather than "just" doing an aggregate over the rows the where clause > tar

Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.

On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote: > so 3. 4. 2021 v 15:38 odesílatel aditya desai napsal: > > "Gather (cost=1000.43..1002.75 rows=1 width=127) (actual > > time=174.318..198.539 rows=1 loops=1)" > > " Workers Planned: 1" > > " Workers Launched: 1" > > " Single Copy: tru

Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.

On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > >> Yes, force_parallel_mode is on. Should we set it off? > > > Yes. I bet someone set it without reading our docs: > > > > > https://www.pos

Re: SHARED LOCKS , EXCLUSIVE LOCKS, ACCESS EXCLUSIVE LOCKS

On Sun, Apr 04, 2021 at 04:12:14PM +0530, aditya desai wrote: > Hi, > We have few select queries during which we see SHARED LOCKS and EXCLUSIVE > LOCKS on tables. Can these locks cause slowness? Is there any way to reduce > the locks? > > What must be causing ACCESS EXCLUSIVE LOCKS when the applic

Re: Is there a way to change current time?

On Thu, Apr 15, 2021 at 09:58:23AM -0400, Bruce Momjian wrote: > On Thu, Apr 15, 2021 at 04:45:44PM +0300, warst...@list.ru wrote: > > Hi, > > > > Is there any way to set time that CURRENT_TIMESTAMP and/or now() will give > > next > > time? (We need it only for testing purposes so if there is an

Re: Most proper partitioning form on an integer column

On Sun, Apr 18, 2021 at 08:07:35PM +0200, Il Mimo di Creta wrote: > I need to partition a table on an integer column, which represents the > month of an event, so 12 distinct values. > I am just wondering if any of you has experience about which is the best > way to go with such a use case, in part

Re: hint in determining effective_io_concurrency

On Thu, Apr 22, 2021 at 03:52:32PM -0400, Bruce Momjian wrote: > On Thu, Apr 22, 2021 at 09:45:15PM +0200, Luca Ferrari wrote: > > Hi all, > > I'm unable to find (apparently) a way to find out a possible value to > > start with for effective_io_concurrency. > > I suspect that benchmarking, e.g., us

Re: hint in determining effective_io_concurrency

On Thu, Apr 22, 2021 at 10:22:59PM +0200, Luca Ferrari wrote: > On Thu, Apr 22, 2021 at 10:15 PM Justin Pryzby wrote: > > Note that the interpretation of this GUC changed in v13. > > https://www.postgresql.org/docs/13/release-13.html > > |Change the way non-default effective_

Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?

On Sat, Apr 24, 2021 at 06:27:08PM +, Simon Connah wrote: > I'm curious, really. I use btrfs as my filesystem on my home systems and am > setting up a server as I near releasing my project. I planned to use btrfs on > the server, but it got me thinking about PostgreSQL 13. Does anyone know if

Re: Error while calling proc with table type from Application (npgsql)

On Thu, Apr 29, 2021 at 02:52:23PM +0530, aditya desai wrote: > Hi, > One of the procs which accept tabletype as parameter gives below error > while being called from Application. Could not find a concrete solution for > this. Can someone help? > > call PROCEDURE ABC (p_optiontable optiontype) Wh

Re: Log number of tuples returned

On Thu, Apr 29, 2021 at 03:01:29PM +, Edson Richter wrote: > Which postgresql logging parameters should I activate to log the “number of > tuples returned” for a query? > I would like to debug some dynamicly generated queries in the system that are > returning a absurd number of tuples (> 2,6

Re: 15x slower PreparedStatement vs raw query

On Sun, May 02, 2021 at 07:45:26PM +, Alex wrote: > PreparedStatement: 15s > Raw query with embedded params: 1s > See issue on github with query and explain analyze: > https://github.com/pgjdbc/pgjdbc/issues/2145 | ..PostgreSQL Version? 12 |Prepared statement |... |Planning Time: 11.596 ms |E

Re: 15x slower PreparedStatement vs raw query

On Mon, May 03, 2021 at 03:18:11PM -0500, Justin Pryzby wrote: > On Sun, May 02, 2021 at 07:45:26PM +, Alex wrote: > > PreparedStatement: 15s > > Raw query with embedded params: 1s > > See issue on github with query and explain analyze: > > https://github.co

Re: Very slow Query compared to Oracle / SQL - Server

On Thu, May 06, 2021 at 04:38:39PM +0200, Semen Yefimenko wrote: > Hi there, > > I've recently been involved in migrating our old system to SQL Server and > then PostgreSQL. Everything has been working fine so far but now after > executing our tests on Postgres, we saw a very slow running query on

Re: Very slow Query compared to Oracle / SQL - Server

On Fri, May 07, 2021 at 05:57:19PM +0200, Semen Yefimenko wrote: > For testing purposes I set up a separate postgres 13.2 instance on windows. > To my surprise, it works perfectly fine. Also indexes, have about 1/4 of > the size they had on 12.6. In pg13, indexes are de-duplicated by default. But

Re: PostgreSQL blocked locks query

On Thu, May 13, 2021 at 01:54:32PM +, Manoj Kumar wrote: > I have query in terms of lock monitoring in PostgreSQL where I am not able to > find a way to figure out what value has been passed in SQL statement (from > JDBC driver as prepared statement). > > I am using PostgreSQL 13 version. >

Re: Partition with check constraint with "like"

On Fri, May 21, 2021 at 02:36:14AM +, Nagaraj Raj wrote: > Thank you. This is a great help.  > But "a" have some records with alpha and numeric.  So then you should make one or more partitions FROM ('1')TO('9'). > example : > insert into mytable values('alpha'),('bravo'); > insert into mytab

Re: AWS forcing PG upgrade from v9.6 a disaster

> Here's the FROM clause that bit me: > >FROM lic_en > JOIN govt_region USING (territory_id, country_id) > LEFT JOIN zip_code USING (territory_id, country_id, zip5) > LEFT JOIN "County" USING (territory_id, country_id, fips_county); I'm guessing that there's a dependency/corre

Re: Page File Size Reached Critical Threshold PostgreSQL V13

On Thu, Jun 10, 2021 at 05:45:45AM +0500, Haseeb Khan wrote: > We have installed PostgreSQL V13 on window’s server 2016, where we kept the > Ram of the Server is 32 GB and disk size is 270 GB.Later we faced some > performance issues regarding the database, after deep dive into it we came up > an

Re: Page File Size Reached Critical Threshold PostgreSQL V13

possibly make a suggestion to add RAM. But I do know that "half" is the worst possible setting for many databases. I suggest to provide some more information, and we can try to suggest a better configuration. https://wiki.postgresql.org/wiki/Slow_Query_Questions On 10-Jun-2021, at 9:2

Re: Page File Size Reached Critical Threshold PostgreSQL V13

sation_date_key = date_dim.date_key > and date_dim.year IN (2020) > group by pd.gender > > On Thu, Jun 10, 2021 at 2:48 PM Justin Pryzby wrote: > > > Can you give an example of a query that performed poorly? > > > > Send the query, and its explain (analyze,buff

Re: Partition column should be part of PK

Declarative partitioning was new in v10 In v11, it was allowed to create an index on a partitioned table, including unique indexes. However it's not a "global" index - instead, it's an "inherited" index. For a unique index, uniqueness is enforced within each individual index. And so global uniquen

Re: slow performance with cursor

On Fri, Jun 25, 2021 at 07:09:31PM +0300, Ayub Khan wrote: > I am using postgresql 12 and using cursors in a stored procedure, > executing procedure which has cursor is slowing down the call. However if I > do not use the cursor and just execute the queries using JDBC (Java client) > it's fast. Is

Re: slow performance with cursor

On Fri, 25 Jun 2021, 19:09 Ayub Khan, wrote: > I am using postgresql 12 and using cursors in a stored procedure, > executing procedure which has cursor is slowing down the call. However if I > do not use the cursor and just execute the queries using JDBC (Java client) > it's fast. > > Is there any

Re: ETL - sql orchestrator is stuck when there is not sleep() between queries

On Thu, Jul 08, 2021 at 01:00:28AM +0200, Allan Barrielle wrote: > All is fine, and can work great. > But sometimes, some queries that used to take about 20 secs to complete can > suddenly end in 5mins. > Important all queries have the same shape -> CREATE TABLE SELECT AS *(a bit > of transform) FR

Re: ETL - sql orchestrator is stuck when there is not sleep() between queries

On Thu, Jul 08, 2021 at 03:49:12PM +0200, Allan Barrielle wrote: > > Is it true that the SELECTs have no joins in them ? > > Yes there is a lot of LEFT JOIN. > > > It'd be very useful to get "explain analyze" for a working query and for a > > stuck query. It sound like the stuck query never fini

Re: Partition column should be part of PK

If I'm not wrong, this is the same thing you asked 2 week ago. If so, why not continue the conversation on the same thread, and why not reference the old thread ? I went to the effort to find the old conversation. https://www.postgresql.org/message-id/20210625042228.gj29...@telsasoft.com If decl

Re: Partition column should be part of PK

On Fri, Jul 09, 2021 at 03:32:46AM +, Nagaraj Raj wrote: > My apologies for making confusion with new thread. Yes its same issue related > to earlier post. > I was trying to figure out  how to ensure unique values for columns > (billing_account_guid, ban). If i add partition key to constraint

Re: Linear slow-down while inserting into a table with an ON INSERT trigger ?

On Fri, Jul 16, 2021 at 11:27:24PM +0200, Tobias Gierke wrote: > CREATE OR REPLACE FUNCTION parent_table_changed() RETURNS trigger LANGUAGE > plpgsql > AS $function$ > BEGIN > UPDATE data_sync SET last_parent_table_change=CURRENT_TIMESTAMP; > RETURN NEW; > END; > $function$ > > I'm trying

Re: Big performance slowdown from 11.2 to 13.3

On Wed, Jul 21, 2021 at 06:50:58PM +, l...@laurent-hasson.com wrote: > The plans are pretty much identical too. I checked line by line and couldn't > see anything much different (note that I have a view over this query). Here > is the V13 version of the plan: > I am out of my wits as to what

Re: Partitioned table statistics vs autoanalyze

On Thu, Jul 22, 2021 at 01:32:51PM +0200, Kamil Frydel wrote: > table_1 and table_2 are hash partitioned using volume_id column. Usually we > make analyze on partitions. We do not make analyze on the partitioned table > (parent). > However, if we run 'analyze' on the partitioned table then planner

Re: Big performance slowdown from 11.2 to 13.3

On Thu, Jul 22, 2021 at 04:16:34PM +, l...@laurent-hasson.com wrote: > Is it fair then to deduce that the total memory usage would be 2,400,305kB + > 126,560kB? Is this what under the covers V11 is consuming more or less? It might be helpful to know how much RAM v11 is using. Could you run t

Re: Big performance slowdown from 11.2 to 13.3

On Thu, Jul 22, 2021 at 04:30:00PM +, l...@laurent-hasson.com wrote: > Hello Justin, > > > log_executor_stats=on; client_min_messages=debug; > > Would the results then come in EXPLAIN or would I need to pick something up > from the logs? If you're running with psql, and client_min_messages=

Re: Big performance slowdown from 11.2 to 13.3

On Thu, Jul 22, 2021 at 09:36:02AM -0700, Peter Geoghegan wrote: > I don't see how it's possible for get_hash_mem() to be unable to > return a hash_mem value that could be represented by work_mem > directly. MAX_KILOBYTES is an annoyingly low limit on Windows, where > sizeof(long) is 4. But that's

Re: Big performance slowdown from 11.2 to 13.3

On Thu, Jul 22, 2021 at 05:26:26PM +, l...@laurent-hasson.com wrote: > I tried this but not seeing max resident size data output. Oh. Apparently, that's not supported under windows.. #if defined(HAVE_GETRUSAGE) appendStringInfo(&str, "!\t%ld k

Re: Performance Issue on a table

On Thu, Jul 22, 2021 at 01:54:25PM +, Manoj Kumar wrote: > Recently we have noticed that in one of our DB instances there is a potential > delay in querying a table from java code. could you please check the attached > log and help understand what is the problem and which direction should be

Re: Performance of lateral join

On Mon, Jul 26, 2021 at 01:56:54PM +, Simen Andreas Andreassen Lønsethagen wrote: > To create the subsets, I (or rather my application) will receive lists of > records which should be matched according to some business logic. Each of > these lists will be read into a temporary table: Easy f

Re: Performance of lateral join

On Tue, Jul 27, 2021 at 09:08:49AM +, Simen Andreas Andreassen Lønsethagen wrote: > >Easy first question: is the temp table analyzed before being used in a > > join ? > > No, I haven't done that. Today, I tried to run > > ANALYZE records_to_filter_on; > > on the same sample data

Re: Query performance !

Please don't cross post to multiple lists like this. Cc: pgsql-...@lists.postgresql.org, pgsql-performance@lists.postgresql.org, pgsql-gene...@lists.postgresql.org, pgsql-ad...@lists.postgresql.org If you're hoping for help on the -performance list, see this page and send the "exp

Re: Slow query because lexeme index not used

On Sat, Aug 07, 2021 at 07:35:25PM +, Alex wrote: > Table "product" has a GIN index on "lexeme" column (tsvector) that is not > used. > > Query that doesn't use lexeme idx:  https://explain.dalibo.com/plan/BlB#plan, > ~8s, ~60.000 blocks needed > > Query forced to use lexeme idx: https://ex

Re: Postgres using the wrong index index

On Tue, Aug 10, 2021 at 12:47:20PM -0400, Matt Dupree wrote: > Here's the plan: https://explain.depesz.com/s/uNGg > > Note that the index being used is Could you show the plan if you force use of the intended index ? For example by doing begin; DROP INDEX indexbeingused; explain thequery; rollba

Re: Postgres using the wrong index index

The rowcount estimate for the time column is bad for all these plans - do you know why ? You're using inheritence - have you analyzed the parent tables recently ? | Index Scan using other_events_1004175222_pim_evdef_67951aef14bc_idx on public.other_events_1004175222 (cost=0.28..1,648,877.92 rows

Re: Postgres using the wrong index index

On Thu, Aug 12, 2021 at 09:38:45AM -0400, Matt Dupree wrote: > > The rowcount estimate for the time column is bad for all these plans - do > > you > > know why ? You're using inheritence - have you analyzed the parent tables > > recently ? > > Yes. I used ANALYZE before posting, as it's one of

Re: Postgres using the wrong index index

On Mon, Aug 16, 2021 at 11:22:44AM -0400, Matt Dupree wrote: > > Is either half of the AND estimated correctly? If you do a query > > with only ">=", and a query with only "<=", do either of them give an > > accurate rowcount estimate ? > > Dropping >= results in the correct index being used. Dro

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

On Sat, Aug 21, 2021 at 02:17:26PM -0400, Tom Lane wrote: > "l...@laurent-hasson.com" writes: > > So you mean that on average, the 4x overhead of exceptions is around what > > you'd expect? > > Doesn't surprise me any, no. Exception recovery has to clean up after > a wide variety of possible er

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

On Sat, Aug 21, 2021 at 02:19:50PM -0500, Justin Pryzby wrote: > As I recall, you're running postgres under a windows VM - I'm not sure if > that's relevant. I tried under a couple hyperv VMs but could not reproduce the issue (only an ~8x difference "with exceptions&qu

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

Could you send SELECT * FROM pg_config() and try to find the CPU model ? I think it's possible the hypervisor is trapping and emulating unhandled CPU instructions. Actually, it would be interesting to see if the performance differs between 11.2 and 11.13. It's possible that EDB compiled 11.13 on

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

On Sun, Aug 22, 2021 at 10:50:47AM -0300, Ranier Vilela wrote: > > Tried to check this with Very Sleepy at Windows 10 (bare metal). > > Not sure it can help if someone can guide how to test this better? > explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as "a" > from sampletest

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

On Sun, Aug 22, 2021 at 08:44:34PM -0300, Ranier Vilela wrote: > > If there is any way I can help further... I am definitely not able to do a > > dev environment and local build, but if we have a windows developer > > reproducing the issue between 11 and 12, then that should help. If someone > > ma

Re: Postgres using the wrong index index

On Mon, Aug 23, 2021 at 08:53:15PM -0400, Matt Dupree wrote: > Is it possible that the row estimate is off because of a column other than > time? I would test this by writing the simplest query that reproduces the mis-estimate. > I looked at the # of events in that time period and 1.8 million is

Re: Using regexp from table has unpredictable poor performance

On Wed, Aug 25, 2021 at 11:47:43AM -0500, Jack Christensen wrote: > I have items that need to be categorized by user defined matching rules. > Trusted users can create rules that include regular expressions. I've > reduced the problem to this example. > I use the following query to find matches: >

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 (workarounds)

On Mon, Aug 30, 2021 at 04:43:23AM +0200, Pavel Stehule wrote: > po 30. 8. 2021 v 2:44 odesílatel l...@laurent-hasson.com napsal: > > At this point, I am not sure how to proceed except to rethink that > > toFloat() function and many other places where we use exceptions. We get > > such dirty data t

Re: Postgres chooses slow query plan from time to time

On Mon, Sep 13, 2021 at 08:19:40AM -0600, Michael Lewis wrote: > Autovacuum will only run for freezing, right? Insert only tables don't get > autovacuumed/analyzed until PG13 if I remember right. Tomas is talking about autovacuum running *analyze*, not vacuum. It runs for analyze, except on parti

Re: Performance for initial copy when using pg_logical to upgrade Postgres

On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote: > At Orcid we're trying to upgrade our Postgres database (10 to 13) using > pg_logical for no downtime. The problem we have is how long the initial > copy is taking for the ~500GB database. If it takes say 20days to complete, > will w

Re: Problem with indices from 10 to 13

On Wed, Sep 29, 2021 at 02:11:15AM +, Daniel Diniz wrote: > How do i increase the statistics target for h.nome_des? > And why uploading the dump at 10 and at 13 is there this difference? It's like ALTER TABLE h ALTER nome_des SET STATISTICS 2000; ANALYZE h; https://www.postgresql.org/docs/cur

Re: Troubleshooting a long running delete statement

On Wed, Oct 06, 2021 at 06:00:07PM +, Dirschel, Steve wrote: > • When I did an explain on the delete I could see it was full scanning > the table. I did a full scan of the table interactively in less than 1 second > so the long runtime was not due to the full tablescan. > I started loo

Re: Lock contention high

On Tue, Oct 12, 2021 at 01:05:12PM +0530, Ashkil Dighin wrote: > Hi, > Lock contention observed high in PostgreSQLv13.3 > The source code compiled with GNC(GCCv11.x) > PostgreSQL version: 13.3 > Operating system: RHEL8.3 > Kernel name:4.18.0-305.10.2.el8_4.x86_64 > RAM Size:512GB > SSD: 1TB > The

Re: Fwd: Query out of memory

On Tue, Oct 19, 2021 at 11:28:46AM +0530, aditya desai wrote: > I am running the below query. Table has 21 million records. I get an Out Of > Memory error after a while.(from both pgadmin and psql). Can someone review Is the out of memory error on the client side ? Then you've simply returned more

Re: performance of analytical query

On Thu, Nov 11, 2021 at 08:20:57PM +0100, Jiří Fejfar wrote: > Hi folks, > > we have found that (probably after VACUUM ANALYZE) one analytical query > starts to be slow on our production DB. Moreover, more or less the same > plan is used on our testing data (how to restore our testing data is > de

Re: performance of analytical query

On Fri, Nov 12, 2021 at 10:55:53AM -0700, Michael Lewis wrote: > On Thu, Nov 11, 2021 at 7:42 PM Justin Pryzby wrote: > > > BTW, we disable nested loops for the our analytic report queries. I have > > never > > been able to avoid pathological plans any other way. >

Re: Need help identifying a periodic performance issue.

On Tue, Nov 16, 2021 at 04:43:25AM +, Robert Creager wrote: > We’re executing the following copy to fill a table with approximately 5k > records, then repeating for a total of 250k records. Normally, this copy > executes < 1 second, with the entire set taking a couple of minutes. The > prob

Re: Need help identifying a periodic performance issue.

On Wed, Nov 17, 2021 at 05:51:05PM +, Robert Creager wrote: > postgres`HeapTupleSatisfiesVisibility+0x42 > postgres`heapgetpage+0x237 > postgres`heapgettup_pagemode+0x5ad > postgres`heap_getnextslot+0x52 > postgres`SeqNext+0x

Re: Need help identifying a periodic performance issue.

On Wed, Nov 17, 2021 at 09:54:14PM +, Robert Creager wrote: > We are able to move up to Postgres 13.5, in our ports tree, if that would > help. We used pg_upgrade to get from 9.6 to 13.3, so that should work fine > going instead to 13.5. We’re almost branching/releasing our code, so it’s >

Re: Need help identifying a periodic performance issue.

On Thu, Nov 18, 2021 at 04:39:42PM +1300, Thomas Munro wrote: > On Thu, Nov 18, 2021 at 1:18 PM Robert Creager > wrote: > > So, how do I go about capturing more information for the big brains (you > > guys) to help figure this out? I have all our resources at mine (and hence > > your) disposal

Re: performance of analytical query

On Fri, Nov 12, 2021 at 09:12:38PM +0100, Jiří Fejfar wrote: > * I know that PG is focused on OLTP rather then analytics, but we are happy > with it at all and do not wish to use another engine for analytical > queries... isn't somewhere some "PG analytical best practice" available? It's a good qu

Re: Need help identifying a periodic performance issue.

On Wed, Nov 24, 2021 at 10:44:12PM +, Robert Creager wrote: > I forgot, I had reloaded postgres, but had not re-started our app, so the > connections wouldn’t have that plan setting on them. Re-doing now. Are you sure? GUC changes should be applied for existing sessions, right ? Would you s

Re: pg_dump backup verification

On Thu, Nov 25, 2021 at 02:41:34PM +0530, Daulat wrote: > Please suggest how I can ensure pg_dump backup has completed successfully ? > I don't think there is any view like Oracle which helps with > dba_datampump_jobs etc. 1) Check its exit status. If it's nonzero, then surely there's a problem (

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Sat, Dec 04, 2021 at 05:32:10PM +, l...@laurent-hasson.com wrote: > I have a data warehouse with a fairly complex ETL process that has been > running for years now across PG 9.6, 11.2 and now 13.4 for the past couple of > months. I have been getting the error "An I/O error occurred while s

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Sat, Dec 04, 2021 at 07:18:06PM +, l...@laurent-hasson.com wrote: > It's a remote server, but all on a local network. Network performance is I am > sure not the issue. Also, the system is on Windows Server. What are you > expecting to see out of a tcpdump? I'll try to get PG logs on the fa

Re: Query is slower with a large proportion of NULLs in several columns

On Mon, Dec 20, 2021 at 08:11:42PM -0800, Lars Bergeson wrote: > ok, here are results after I did: > set max_parallel_workers_per_gather = 0; > > HashAggregate (cost=1676432.13..1676432.16 rows=3 width=15) (actual > time=19908.343..19908.345 rows=5 loops=1) > I/O Timings: read=532369.898 > Exe

Re: Query is slower with a large proportion of NULLs in several columns

On Tue, Dec 21, 2021 at 12:33:06AM -0500, Tom Lane wrote: > So now we have a real mystery about what is happening on Lars' > system. Those numbers can't be right. I realized Lars said it was x86_64/Linux, but I'm hoping to hear back with more details: What OS version? Is it a VM of some type ? H

Re: slow "select count(*) from information_schema.tables;" in some cases

On Mon, Feb 07, 2022 at 04:56:35PM +, Lars Aksel Opsahl wrote: > Sometimes simple sql's like this takes a very long time "select count(*) > from information_schema.tables;" > > Other sql's not including system tables may work ok but login also takes a > very long time. > > The CPU load on

Re: Slow Running Queries in Azure PostgreSQL

On Tue, Feb 22, 2022 at 02:11:58PM +, Kumar, Mukesh wrote: > -> Hash Join (cost=6484.69..43117.63 rows=1 width=198) (actual > time=155.508..820.705 rows=52841 loops=1)" >Hash Cond: (((lms_doc_property_rights_assoc.doc_sid_c)::text = > (lms_doc_propright_status_assoc.doc_sid_c)::te

<    1   2   3   4   5   >