Re: Batch insert heavily affecting query performance.

On Wed, Dec 27, 2017 at 10:13 AM, Jean Baro wrote: > Hello, > > We are still seeing queries (by UserID + UserCountry) taking over 2 > seconds, even when there is no batch insert going on at the same time. > > Each query returns from 100 to 200 messagens, which would be a 400kb

PG 10 hash index create times

Since upgrading to PG 10 a few weeks ago I've been experimenting with hash indexes. One thing I've noticed is that they seem to take a _lot_ longer to create than btree indexes, particularly on large tables. I've got a moderately sized table of about 38M rows and the create index using hash for

Re: postgresql 10.1 wrong plan in when using partitions bug

On Sun, Feb 4, 2018 at 8:19 AM, legrand legrand wrote: > What is the value of guc constrain_exclusion ? > > > In my use case, which is a big union all behind a view, setting this to off, on, or partition makes no difference. It still sequence scans all of the

Re: postgresql 10.1 wrong plan in when using partitions bug

On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Rick Otten <rottenwindf...@gmail.com> 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 unione

Re: postgresql 10.1 wrong plan in when using partitions bug

On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky < mariel.cherkas...@gmail.com> wrote: > > Hi, > I configured range partitions on a date column of my main table(log_full). > Each partition represents a day in the month. Every day partition has a > list parition of 4 tables on a text column. > >

Re: effective_io_concurrency on EBS/gp2

On Thu, Feb 8, 2018 at 11:40 AM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > Anyway, there are still some strange things happening when > effective_io_concurrency is non-zero. > > ... > > Vitaliy > > I was researching whether I could optimize a concatenated lvm2 volume when I have

blending fast and temp space volumes

Some of my data processes use large quantities of temp space - 5 or 6T anyway. We are running in Google Cloud. In order to get the best performance out of all of my queries that might need temp space, I've configured temp space on a concatenated local (volatile) SSD volume. In GCE, local SSD's

primary key hash index

After reading this article about keys in relational databases, highlighted on hacker news this morning: https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html I keep pondering the performance chart, regarding uuid insert, shown towards the bottom of the article. I believe he was doing that

Re: badly scaling performance with appending to bytea

Can you use a materialized view to do the bytea_agg() and then refresh concurrently whenever you need updated data? The refresh concurrently might take a few hours or days to run to keep the matview up to date, but your queries would be pretty fast. A possible problem is that you are running out

Re: Why Postgres doesn't use TID scan?

On Wed, Dec 19, 2018 at 6:45 PM Vladimir Ryabtsev wrote: > > The fundamental issue is that "ANY" has two meanings in PG, one of them > following the SQL standard and one not: > > Oh yes, I was aware about two forms but it did not come into my mind, I > was thinking I use the same form in both

Re: Database size 1T but unclear why

On Sun, Dec 9, 2018 at 10:19 AM Mariel Cherkassky < mariel.cherkas...@gmail.com> wrote: > Hi, > I'm trying to understand why my database consume so much space. I checked > the space it consume on disk : > > Have you tried running pg_repack? (It is an extension.)

Re: How Do You Associate a Query With its Invoking Procedure?

On Fri, Sep 14, 2018 at 12:34 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Sep 13, 2018 at 12:49 PM, Fd Habash wrote: > >> In API function may invoke 10 queries. Ideally, I would like to know what >> queries are invoked by it and how long each took. >> >> >> >> I’m using

Re: Oracle to postgres migration

On Mon, Apr 8, 2019 at 8:04 AM Julien Rouhaud wrote: > On Mon, Apr 8, 2019 at 1:49 PM Daulat Ram > wrote: > > > > Please confirm ! Can we migrate Oracle 12c database (12.1.0.1.0) running > on Solaris to PostgreSQL 11.2 on Linux (Ubuntu). Also, please suggest the > tools and pre-requisites. > A

Re: materialized view refresh of a foreign table

On Sun, Jun 23, 2019 at 10:21 AM Rick Otten wrote: > I'm not sure where else to look, so I'm asking here for tips. > > I have a table in a remote (Heroku-managed) postgresql database (PG 10.7). > > On the other end, (PG 11.3) I have a foreign table configured with a > material

materialized view refresh of a foreign table

I'm not sure where else to look, so I'm asking here for tips. I have a table in a remote (Heroku-managed) postgresql database (PG 10.7). On the other end, (PG 11.3) I have a foreign table configured with a materialized view in front of it. Up until Thursday evening, it was taking about 12 - 15

Re: Max_connections limit

On Wed, Jun 26, 2019 at 5:16 AM Hervé Schweitzer (HER) < herve.schweit...@dbi-services.com> wrote: > You now that Postgres don’t have any shared_pool as Oracle, and the > session information ( execution plan, etc..) are only available for the > current session. Therefore I also highly recommend

Re: PSQL performance - TPS

On Thu, Aug 1, 2019 at 2:15 PM Andres Freund wrote: > Hi, > > On 2019-08-01 23:36:33 +0530, Purav Chovatia wrote: > > > If you've set synchronous_commit = off, and you still get only 1200 > > > transactions/sec, something else is off. Are you sure you set that? > > I am not very surprised with

Re: High concurrency same row (inventory)

On Mon, Jul 29, 2019 at 2:16 AM Jean Baro wrote: > > We have a new Inventory system running on its own database (PG 10 AWS > RDS.m5.2xlarge 1TB SSD EBS - Multizone). The DB effective size is less than > 10GB at the moment. We provided 1TB to get more IOPS from EBS. > > As we don't have a lot of

Re: benchmarking effective_io_concurrency

On Mon, Jul 22, 2019 at 2:42 AM Fabio Pardi wrote: > Hello, > > > I recently spent a bit of time benchmarking effective_io_concurrency on > Postgres. > > I would like to share my findings with you: > > > https://portavita.github.io/2019-07-19-PostgreSQL_effective_io_concurrency_benchmarked/ > >

Re: Slow "not in array" operation

On Wed, Nov 13, 2019 at 5:47 AM Morris de Oryx wrote: > Disclaimer: Out over my skis again. > > From what you say here, and over on SO, it sounds like you've got two > problems: > > * Matching on *huge *numbers of records because of common tags. > > * A dynamic collection of tags as they're

Re: Would SSD improve Index Only Scan performance by a lot?

On Tue, Oct 8, 2019 at 7:37 PM Arya F wrote: > As my table has gotten bigger, it takes longer to get a single row back > when querying a row by its btree index. > > Right now the database is running on a traditional HDD. SSDs have a much > faster seek time than traditional HDDs. > > Would

Re: Best way to delete big amount of records from big table

On Fri, Mar 27, 2020 at 10:14 AM Ekaterina Amez wrote: > > it's there a better way to do this. I'm testing on version 9.2 BUT > production server is 8.4 (legacy application, supposed to be in at least > 9.2 but recently discovered it was 8.4, planning upgrade but not now). > Config parameters

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

On Thu, May 6, 2021 at 10:38 AM 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 a >

Re: 15x slower PreparedStatement vs raw query

On Tue, May 4, 2021 at 6:05 AM Alex wrote: > Shouldn't this process be automatic based on some heuristics? > > Saving 10ms planning but costing 14s execution is catastrophic. > > For example, using some statistics to limit planner time to some percent > of of previous executions. > This way, if

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

On Tue, Mar 23, 2021 at 2:52 AM Paul McGarry wrote: > > > On Tue, 23 Mar 2021 at 16:13, Justin Pryzby wrote: > >> 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

Re: Potential performance issues

On Mon, Mar 1, 2021 at 8:44 AM Bob Jolliffe wrote: > Was just about to reply similarly. Mind you it perhaps does raise the > question : are the default postgresql settings perhaps too > conservative or too static. For example, in the absence of other > explicit configuration, might it make

Re: Logical Replication speed-up initial data

On Thu, Aug 5, 2021 at 12:57 AM Nikhil Shetty wrote: > Hi, > > Thank you for the suggestion. > > We tried by dropping indexes and it worked faster compared to what we saw > earlier. We wanted to know if anybody has done any other changes that helps > speed-up initial data load without dropping

Re: postgresql13-llvm jit-13.5-1PGDG.rhel8.x86_64

On Mon, Nov 15, 2021 at 8:57 AM Mladen Gogala wrote: > > On 11/15/21 00:04, Pavel Stehule wrote: > > > Hi > > >> There is a CentOS8-stream version which solves the problem but I cannot >> use that in the office. I will probably have to wait for another month >> before OL8 has everything that I

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

On Tue, Aug 29, 2023 at 3:57 PM Rondat Flyag wrote: > I took the dump just to store it on another storage (external HDD). I > didn't do anything with it. > > 29.08.2023, 21:42, "Jeff Janes" : > > > > On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag > wrote: > > I have a legacy system that uses

Re: data consolidation: logical replication design considerations

On Sat, Jul 16, 2022 at 12:07 PM Rory Campbell-Lange < r...@campbell-lange.net> wrote: > I'd be grateful for some comments on the advisability of using a large > number of concurrent logical replication publications/subscriptions. > Below I've set out the current environment and a suggested

Re: Postgresql 14 partitioning advice

On Wed, Jul 27, 2022 at 8:55 AM Rick Otten wrote: > I'm spinning up a new Postgresql 14 database where I'll have to store a > couple years worth of time series data at the rate of single-digit millions > of rows per day. Since this has to run in AWS Aurora, I can't use > Times

Postgresql 14 partitioning advice

I'm spinning up a new Postgresql 14 database where I'll have to store a couple years worth of time series data at the rate of single-digit millions of rows per day. Since this has to run in AWS Aurora, I can't use TimescaleDB. I've been soliciting advice for best practices for building this.

Re: Identify root-cause for intermittent spikes

I like to use pgbadger to collect data on what is happening in RDS instances. You have to turn up a bunch of logging in RDS: 1. Turn on connection logging, duration logging, lock_waits, and anything else that you are interested in studying. 2. Then grab all of your postgresql logs from AWS. I

Re: to_jsonb performance on array aggregated correlated subqueries

On Fri, Aug 12, 2022 at 3:02 PM Rick Otten wrote: > > > On Fri, Aug 12, 2022 at 2:50 PM Nico Heller wrote: > >> Good day, >> >> consider the following query: >> >> WITH aggregation( >> SELECT >> a.*, >>

Re: Postgresql 14 partitioning advice

On Mon, Aug 1, 2022 at 10:16 AM Rick Otten wrote: > >> The other problem I ran into, which I'm still building a test case for >> and I fear might be a bug if I can easily reproduce it, >> is if I did the original select in a CTE, and then did a sort outside of >> t

Re: Postgresql 14 partitioning advice

> > > The other problem I ran into, which I'm still building a test case for and > I fear might be a bug if I can easily reproduce it, > is if I did the original select in a CTE, and then did a sort outside of > the CTE, even though the CTE found 0 rows, the database > still spent a _ton_ of time

creating hash indexes

I inherited a database with several single-digit billion row tables. Those tables have a varchar(36) column populated with uuids (all connected to each other via FKs) each currently supported by a btree index. After the recent conversations about hash indexes I thought I'd do some comparisons to

Fwd: Database Stalls

On Mon, Jan 30, 2023 at 4:32 PM Mok wrote: > Hi, > > Unfortunately there is no pg_stat_activity data available as we are > unaware of the issue until it has already happened. > > The version we are on is 12.11. > > I don't think it is due to locks as there are none in the logs. Vacuums > are

Re: LIKE CLAUSE on VIEWS

> > > On Sun, 22 Jan 2023 at 13:34, aditya desai wrote: > >> Hi, >> Is there any way to improve performance of LIKE clause on VIEWS. >> >> select * From request_vw where upper(status) like '%CAPTURED%' - 28 >> seconds. >> >> select * from request_vw where status='CAPTURED' >> >> Application team

Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance

I was able to reproduce a similar issue with using `= ANY(VALUES)` > instead of `= ANY(ARRAY)`: > > 1. fast query using =ANY(ARRAY): https://explain.depesz.com/s/dwP8 > 2. slow query using =ANY(ARRAY(expr)): https://explain.depesz.com/s/3hGb > 3. slow query using =ANY(VALUES):

Re: Huge Tables

300M rows isn't "huge", but it is starting to get to be real data. Some notes/very general rules of thumb since you asked a very general question: 1. Consider updating the statistics on the table from the default sample of 100 rows to something larger - especially if you have a wide variety of

max_wal_senders

I've been thinking about the max_wal_senders parameter lately and wondering if there is any harm in setting it too high. I'm wondering if I should try to shave a few senders off, perhaps to match my logical replicas + 1, instead of just leaving it at the default of 10. Or vice-versa, can clients