Re: Query is slow when run for first time; subsequent execution is fast

Nandakumar M schrieb am 12.01.2018 um 09:03: > Even if I close a connection and open a new one and execute the same > query, the planning time is considerably less than the first time. > Only when I restart the Postgres server then I face high planning > time again. Yes, because the data is cached

Performance impact of lowering max_files_per_process

We have a customer project where Postgres is using too many file handles during peak times (around 150.000) Apart from re-configuring the operating system (CentOS) this could also be mitigated by lowering max_files_per_process. I wonder what performance implications that has on a server with a

Re: Performance impact of lowering max_files_per_process

Thomas Kellerer schrieb am 19.01.2018 um 17:48: > > I wonder what performance implications that has on a server with > around 50-100 active connections (through pgBouncer). > > My understanding of the documentation is, that Postgres will work > just fine if we lower the limit,

Re: Should from_collapse be switched off? (queries 10 times faster)

Peter schrieb am 23.03.2018 um 11:03: > My queries get up to 10 times faster when I disable from_collapse > (setting from_collapse_limit=1). > > After this finding, The pramatic solution is easy: it needs to be > switched off. You should post some example queries together with the slow and fast p

Re: propose web form for submission of performance problems

Craig James schrieb am 25.05.2018 um 03:27: > 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 th

Re: PostgreSQL VS MongoDB: a use case comparison

Stephen Frost schrieb am 20.11.2018 um 18:28: > Oh yes, having a dictionary would be a great start to reducing the size > of the jsonb data, though it could then become a contention point if > there's a lot of new values being inserted and such. Naturally there > would also be a cost to pulling th

Re: Detect missing combined indexes (automatically)

Thomas Güttler schrieb am 10.01.2019 um 13:56: > Is there a way to detect missing combined indexes automatically > > I am managing a lot of databases and I think a lot of performance > could get gained. > > But I don't want to do this manually. > > My focus is on missing combined indexes, since

Bloom index cost model seems to be wrong

I stumbled upon this question: https://dba.stackexchange.com/questions/229413 in a nutshell: the bloom index is not used with the example from the manual. The bloom index is only used if either Seq Scan is disabled or if the random_page_cost is set to 1 (anything about 1 triggers a Seq Sca

Re: Pg10 : Client Configuration for Parallelism ?

laurent.decha...@orange.com schrieb am 17.04.2019 um 08:30: > I am working on PostgreSQL 10.5 and I have a discrepancy between clients > regarding parallelism feature. > > For a simple query (say a simple SELECT COUNT(*) FROM BIG_TABLE), I > can see PostgreSQL use parallelism when the query is la

Re: Pg10 : Client Configuration for Parallelism ?

laurent.decha...@orange.com schrieb am 17.04.2019 um 16:33: Hello Justin and thank you for your clues. Finally I found that putting blank to the option that limits the number of rows to retrieve (which is normal for this kind of tool) allows PostgreSQL to parallelize the query. On jdbc it seems

Re: Optimizing `WHERE x IN` query

Omar Roth schrieb am 07.07.2019 um 15:43: Currently, the query I'm using to generate a user's feed is: ``` SELECT * FROM channel_videos WHERE ucid IN (SELECT unnest(subscriptions) FROM users WHERE email = $1) ORDER BY published DESC; ``` You could try an EXISTS query without unnest: select cv

Strange runtime partition pruning behaviour with 11.4

I stumbled across this question on SO: https://stackoverflow.com/questions/56517852 Disregarding the part about Postgres 9.3, the example for Postgres 11 looks a bit confusing. There is a script to setup test data in that question: start of script create table foo ( fo

Re: Strange runtime partition pruning behaviour with 11.4

it's posible to rewrite the query to: test=# explain analyse select count(*) from foo_bar_baz as fbb where foo_id = (select foo_id from foo where foo_name = 'eeny'); I know, that's not a solution, but a workaround. :-( Yes, I discovered that as well. But I'm more confused (or concerned) by

Re: Strange runtime partition pruning behaviour with 11.4

Tom Lane schrieb am 03.08.2019 um 18:05: > Yeah, I get the same plan with or without ANALYZE, too. In this example, > having the ANALYZE stats barely moves the rowcount estimates for > foo_bar_baz at all, so it's not surprising that the plan doesn't change. > (I do wonder how Thomas got a differen

Surprising benchmark count(1) vs. count(*)

https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ Is there a reason why count(*) seems to be faster?

Re: Surprising benchmark count(1) vs. count(*)

Laurenz Albe schrieb am 19.09.2019 um 12:22: >> https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ >> >> Is there a reason why count(*) seems to be faster? > > "count(*)" is just the SQL standard's way of saying what you'd > normally call "count()", that is, an aggregate without argume

Re: Considerable performance downgrade of v11 and 12 on Windows

Eugene Podshivalov schrieb am 29.11.2019 um 11:04: > Imported ways data from a file and added a primary key. > > SET synchronous_commit TO OFF; > COPY ways FROM 'E:\ways.txt'; > ... > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; Those two commands are not doing the same thing - the piping

Re: Bad query plan when you add many OR conditions

Marco Colli schrieb am 10.01.2020 um 02:11: > I have a query on a large table that is very fast (0s): > https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt > > Basically the query matches the rows that have a tag1 OR tag2 OR tag3 OR tag4 > OR tag5...  > > Howe

Re: JOIN on partitions is very slow

Michael Lewis schrieb am 23.03.2020 um 17:16: Yes. I can tweak the query. Version of postgres is 9.5.15. I have about 20 partitions for company_sale_account table. I do have an index on company name. I need to use DISTINCT as i need to remove the duplicates. DISTINCT is a sign of improper join

Re: Postgres not using index on views

Rick Vincent schrieb am 07.04.2020 um 11:08: > The function is defined as below, so no use of VOLATILE. If you don't specify anything, the default is VOLATILE. So your function *is* volatile.   > CREATE OR REPLACE FUNCTION extractValueJS (sVar text, nfm INTEGER, nvm > INTEGER) > RETURNS VARCHAR

Re: Postgres not using index on views

> RV>> It simply is the way the application stores the data. For Oracle > we are storing in XML and JSON format, for postgres, due do > limitations of XML api, we are storing in VARCHAR. Why not use JSON in Postgres then? Postgres' JSON functions are at least as powerful as Oracle's (if not bette

Re: PostgreSQL does not choose my indexes well

> CREATE INDEX idx_tabla_entidad >     ON public.entidad USING btree >     (cod_tabla ASC NULLS LAST); > > CREATE INDEX idx_entidad_tabla_4 >     ON public.entidad USING btree >     (cod_entidad_tabla ASC NULLS LAST) >     INCLUDE(cod_entidad, cod_tabla, cod_entidad_tabla) >     WHERE cod_tabla::bi

Re: Query performance issue

Nagaraj Raj schrieb am 04.09.2020 um 23:18: I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps. EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select ser

Re: Potential performance issues

Jung, Jinho schrieb am 28.02.2021 um 16:04: > # Performance issues discovered from differential test > > For example, the below query runs x1000 slower than other DBMSs from > PostgreSQL. > >     select ref_0.ol_amount as c0 >     from order_line as ref_0 >         left join stock as ref_1 >      

Re: Partition column should be part of PK

David Rowley schrieb am 12.07.2021 um 02:57: > Generally, there's not all that much consensus in the community that > this would be a good feature to have. Why do people want to use > partitioning? Many people do it so that they can quickly remove data > that's no longer required with a simple

Re: Performance benchmark of PG

Manish Lad schrieb am 19.07.2021 um 12:09: > We are planning to migrate Oracle exadata database to postgresql and > db size ranges from 1 tb to 60 TB. > > Will the PG support this with the performance matching to that of > exadata applince? If anyone could point me in the right direction > where i

Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle

Bruce Momjian schrieb am 08.10.2021 um 17:21: However, I also need to ask how the wait event information, whether tracing or sampling, can be useful for Postgres because that will drive the solution. I guess everyone will use that information in a different way. We typically use the AWR report

Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle

Bruce Momjian schrieb am 08.10.2021 um 17:40: I guess everyone will use that information in a different way. We typically use the AWR reports as a post-mortem analysis tool if something goes wrong in our application (=customer specific projects) E.g. if there was a slowdown "last monday" or "sa

Re: Out of memory error

aditya desai schrieb am 24.11.2021 um 07:25: > Thanks Tom. However I could not find any solution to achieve the given > requirement. I have to take all values in the temp table and assign it to an > array variable to pass it to the audit procedure as shown below. Can you > please advise ?  > > C

Re: Out of memory error

aditya desai schrieb am 24.11.2021 um 08:35: > Hi Thomas, > v_message is of composite data type r_log_message and it's definition is as > shown below. > > postgres=# \d r_log_message; >                  Composite type "public.r_log_message" >    Column    |          Type           | Collation | Nu

Re: Out of memory error

aditya desai schrieb am 24.11.2021 um 08:31: > H Michael, > Please see insert_info function below. Also r_log_message is composite data > type and it's definition is also given below. > > CREATE OR REPLACE FUNCTION insert_info( >     info_array  r_log_message[] > ) RETURNS varchar AS $$ >     DECL

Query runs slower as prepared statement - identical execution plans

Hello, I have a strange case of a query that runs substantially slower when run as a Java PreparedStatement with placeholders, compared to using constant values in the SQL string. In my experience, the reason for this is usually a different execution plan for the prepared statement. However in

Re: Terribly slow query with very good plan?

Les schrieb am 04.02.2022 um 10:11: > My task is to write a query that tells if a folder has any active file inside > it - directly or in subfolders. Here is the query for that: > > EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) > > select id, title, > (exists (select f2.id from media.