Re: Extremely slow to establish connection when user has a high number of roles

On Sat, Apr 20, 2024, 5:25 PM Michal Charemza wrote: > Hi, > > We're running PostgreSQL as essentially a data warehouse, and we have a > few thousand roles, which are used to grant permissions on a table-by-table > basis to a few thousand users, so a user would typically have say between 1 > and

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

are you referring to this SQL SERVER User Defined Table Type and Table Valued Parameters - SqlSkull I have not used/heard a similar *select * from custom_type* so just trying to help :) // src table

Re: 15x slower PreparedStatement vs raw query

I am not an expert on this, But I would like to take a shot :) Is it possible to share your prepared statement and parameter types. I mean something like this PREPARE usrrptplan (int) AS SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2; EXECUTE usrrptpla

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

I am not sure, if the goal is just for the specific set of predicates or performance in general. Also from the explain plan, it seems there is still a significant amount of buffers read vs hit. That would constitute i/o and may add to slow result. What is the size of the table and the index ? Is

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

ok one last thing, not to be a PITA, but just in case if this helps. postgres=# SELECT * FROM pg_stat_user_indexes where relname = 'logtable'; postgres=# SELECT * FROM pg_stat_user_tables where relname = 'logtable'; basically, i just to verify if the table is not bloated. looking at *n_live_tup* v

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

esting, I've also been running VACUUM and ANALYZE pretty much before > every test run. > > Am Fr., 7. Mai 2021 um 10:44 Uhr schrieb Vijaykumar Jain < > vijaykumarjain.git...@gmail.com>: > >> ok one last thing, not to be a PITA, but just in case if this helps. >> >

Re: Partition with check constraint with "like"

just out of curiosity, what would a typical query be ? select * from t1 where name = somename ? == equality match // if yes, hash partitioning may be helpful to a have reasonably balanced distribution or select * from t1 where name like 'some%'; what would be the distribution of rows for

Re: transaction blocking on COMMIT

I think there have been similar issues reported earlier as well. But it would be too early to generalize. Where is the db server running? Cloud? Also what is the version ? On Mon, May 24, 2021, 5:00 PM Bob Jolliffe wrote: > I am seeing a strange issue on a database using jdbc. Regularly, 4

Re: transaction blocking on COMMIT

ure of the underlying hyperviser. I could find out. > > Regards > Bob > > > On Mon, 24 May 2021 at 12:35, Vijaykumar Jain > wrote: > > > > I think there have been similar issues reported earlier as well. But it > would be too early to generalize. > > > >

Re: PgSQL 12 on WinSrv ~3x faster than on Linux

also if you can setup an external timer \timing , along with explain analyse to get total time, it would help if everything else is same. I have seen some threads thar mention added startup cost for parallel workers on windows but not on Linux. But I do not want to mix those threads here, but jus

Re: query planner not using index, instead using squential scan

thanks Tom. I was trying to simulate some scenarios to be able to explain how the plan would change with/without *Rows Removed by Filter: 73969 * -- by using a different/correct index. postgres=# \d t Table "public.t" Column |Type | Coll

Re: query planner not using index, instead using squential scan

-> Index Scan Backward using order_offer_map_order_id on > order_offer_map oom (cost=0.43..54779.81 rows=1273009 width=15) (actual > time=0.010..578.226 rows=1273009 loops=1) > Planning Time: 1.229 ms > Execution Time: 3183.248 ms > > On Sat, Jun 5, 2021 at 10

Re: waiting for client write

Ayub, Ideally when i have to deal with this, i run a pgbench stress test locally on the db server on lo interface which does not suffer mtu / bandwidth saturation issues. then run the same pgbench from a remote server in the same subnet as the app and record the results and compare. that helps me

Re: waiting for client write

since you are willing to try out options :) if your setup runs the same test plan queries on jmeter against oracle and postgresql and only postgresql shows waits or degraded performance I think this is more then simply network. can you simply boot up an ec2 ubuntu/centos and install postgresql. a

Re: waiting for client write

thanks. >latency average = 2480.042 ms that latency is pretty high, even after changing the mtu ? for a query that takes 5ms to run (from your explain analyze above) and returns a few 100 rows. so it does look like a network latency, but it seems strange when you said the same query from the sam

Re: waiting for client write

On Tue, 15 Jun 2021 at 21:13, Ayub Khan wrote: > > > Would it be a cursor issue on postgres, as there seems to be a difference in how cursors are handled in postgres and Oracle database. It seems cursors are returned as buffers to the client side. Below are the steps we take from jdbc side i did

Re: Estimating wal_keep_size

On Fri, 18 Jun 2021 at 23:58, Julien Rouhaud wrote: > Le sam. 19 juin 2021 à 02:13, Dean Gibson (DB Administrator) < > postgre...@mailpen.com> a écrit : > >> >> Granted, but the same question arises about the value for >> max_slot_wal_keep_size. Setting either too low risks data loss, & setting

Re: Big performance slowdown from 11.2 to 13.3

Just asking, I may be completely wrong. is this query parallel safe? can we force parallel workers, by setting low parallel_setup_cost or otherwise to make use of scatter gather and Partial HashAggregate(s)? I am just assuming more workers doing things in parallel, would require less disk spill pe

Re: Big performance slowdown from 11.2 to 13.3

On Fri, 23 Jul 2021 at 03:06, l...@laurent-hasson.com wrote: > I am not sure I understand this parameter well enough but it’s with a > default value right now of 1000. I have read Robert’s post ( > http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html) > and could play with tho

Re: Logical Replication speed-up initial data

On Thu, 5 Aug 2021 at 10:27, 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 index

Re: Logical Replication speed-up initial data

On Fri, 6 Aug 2021 at 00:15, Nikhil Shetty wrote: > Hi Vijaykumar, > > Thanks for the details. > In this method you are saying the pg_basebackup will make the initial load > faster ? > We intend to bring only a few tables. Using pg_basebackup will clone an > entire instance. > yeah. In that case

Re: Same query 10000x More Time

On Thu, 6 Jan 2022 at 13:13, Avi Weinberg wrote: > Hi > > > > I have postgres_fdw table called tbl_link. The source table is 2.5 GB in > size with 122 lines (some lines has 70MB bytea column, but not the ones I > select in the example) > > I noticed that when I put the specific ids in the list "

Re: Same query 10000x More Time

On Thu, Jan 6, 2022, 3:50 PM Avi Weinberg wrote: > Hi Kyotaro Horiguchi and Vijaykumar Jain, > > Thanks for your quick reply! > > I understand that the fact the slow query has a join caused this problem. > However, why can't Postgres evaluate the table of the "IN

Re: Same query 10000x More Time

On Thu, 6 Jan 2022 at 20:01, Avi Weinberg wrote: > Thanks for the input > > > > postgres_fdw seems to bring the entire table even if all I use in the join > is just the id from the remote table. I know it is possible to query for > the missing ids and then perform the delete, but I wonder why al

Re: Terribly slow query with very good plan?

s=1) InitPlan 1 (returns $0) -> Index Scan using path_gist_idx on test (cost=0.13..8.15 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (path ~ '*.Stars'::lquery) Planning Time: 0.079 ms Execution Time: 0.037 ms (6 rows) Please ignore, if not relevant to the discussion. -- Thanks, Vijay LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>

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

On Mon, Feb 7, 2022, 10:26 PM Lars Aksel Opsahl wrote: > Hi > > 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 the ser

Re: Postgresql 14/15/16/17 partition pruning on dependent table during join

On Fri, 1 Nov 2024 at 18:51, Stepan Yankevych wrote: > > Partition pruning is not pushing predicate into dependent table during join > in some cases. > See example. Predicate highlighted in red > i think your observation is correct. you may need to provide redundant predicates for join both tabl