On Mon, Jan 1, 2024 at 9:57 AM Darwin Correa wrote:
> Hello, Happy New Year! I add my responses in blue.
>
>
>
> El Thu, 28 Dec 2023 13:06:18 -0500, *Jeff Janes
> >* escribió
>
> I thought the point of sharding was to bring more CPU and RAM to bear than
> *1st time query executed with PARALLEL DEGREE 2 *
> explain analyze select /*+* PARALLEL(A 2)* */ * from
> test_compare_all_col_src1 A;
> QUERY
> PLAN
>
>
On Tue, Jan 2, 2024 at 1:29 PM Jerry Brenner wrote:
> We are currently on 13.9.
>
Why not just use the latest minor release, 13.13? For security reasons,
that is the only minor release of v13 you should be using anyway. I think
it is a bit much to hope that people will spend their time for
On Thu, Dec 28, 2023 at 7:47 AM mohini mane
wrote:
> Thank you for your response !!
> I am experimenting with SQL query performance for SELECT queries on large
> tables and I observed that changing/increasing the degree of parallel hint
> doesn't give the expected performance improvement.
>
But
On Thu, Dec 28, 2023 at 12:03 PM Darwin Correa wrote:
>
> when run the query (query.sql) as you can see in explain (plan4_v3.txt)
> citus take about 18s to run all fragments
>
Where is plan4_v3.txt? Is that hidden in some non-obvious way in one of
your links?
> but each fragment take at
On Wed, Dec 27, 2023 at 8:15 AM mohini mane
wrote:
> Hello Team,
> I observed that increasing the degree of parallel hint in the SELECT
> query did not show performance improvements.
> Below are the details of sample execution with EXPLAIN ANALYZE
>
PostgreSQL doesn't have hints, unless you are
On Sat, Dec 2, 2023 at 11:50 AM Jean-Christophe Boggio <
postgre...@thefreecat.org> wrote:
> Hello,
>
> I am trying to optimize a complex query and while doing some explains, I
> stumbled upon this :
>
>CTE cfg
> -> Result (cost=2.02..2.03 rows=1 width=25) (actual
>
On Sun, Nov 5, 2023 at 11:20 AM Abraham, Danny
wrote:
> Thanks Laurenz,
>
> Traced two huge plans. They differ.
> The fast one does use Materialize and Memoize (the psql).
> Is there something in JDBC 42 that blocks these algoruthms?
Directly blocking those is not likely. Maybe the way the
On Mon, Oct 23, 2023 at 6:33 AM Tomasz Szymański wrote:
> Limit (cost=0.00..1184.30 rows=21 width=4) (actual
> time=1567.136..1619.956 rows=1 loops=1)
>-> Seq Scan on account_user (cost=0.00..256768.27 rows=4553 width=4)
> (actual time=1567.135..1619.953 rows=1 loops=1)
>
It thinks
On Tue, Oct 17, 2023 at 10:09 AM Tomasz Szymański wrote:
> - Database version: 11.18
That is pretty old. It is 3 bug-fix releases out of date even for its
major version, and the major version itself is just about to reach EOL and
is missing relevant improvements.
- Plan when it uses an index
On Fri, Sep 22, 2023 at 5:44 AM Koen De Groote wrote:
> Alright.
>
> So, if I want to speed up the query, apart from trying to vacuum it
> beforehand, I suspect I've hit the limit of what this query can do?
>
It is more a limit on the system as a whole, not just one query. How is
this table
On Thu, Aug 31, 2023 at 11:06 AM Maxim Boguk wrote:
> With the default value of autovacuum_vacuum_scale_factor (The default is
> 0.2 (20% of table size).) index will collect like 100M outdated/dead index
> entries before autovacuum kicks in and cleans them all (in a worst case),
> and of course
On Wed, Aug 30, 2023 at 8:43 PM jayaprabhakar k
wrote:
>
>
> On Tue, Aug 29, 2023, 12:43 PM Jeff Janes wrote:
>
>> On Mon, Aug 28, 2023 at 8:33 PM jayaprabhakar k
>> wrote:
>>
>>>
>>> Since we are only interested in
On Wed, Aug 30, 2023 at 1:31 PM Rondat Flyag wrote:
> Hi and thank you for the response.
>
> I tried VACUUM ANALYZE for three tables, but without success. I also tried
> to set enable_seqscan=off and the query took even more time. If I set
> enable_sort=off then the query takes a lot of time and
On Tue, Aug 29, 2023 at 2:55 PM Rondat Flyag wrote:
> I took the dump just to store it on another storage (external HDD). I
> didn't do anything with it.
>
I don't see how that could cause the problem, it is probably just a
coincidence. Maybe taking the dump held a long-lived snapshot open
On Mon, Aug 28, 2023 at 8:33 PM jayaprabhakar k
wrote:
> Hi,
>
> TL;DR:
> Observations:
>
>1. REINDEX requires a full table scan
> - Roughly create a new index, rename index, drop old index.
> - REINDEX is not incremental. running reindex frequently does not
> reduce the
On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag wrote:
> I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`.
> Everything was fine several days ago even with standard Postgresql
> settings. I dumped a database with the compression option (maximum
> compression level -Z 9) in order
On Tue, Aug 15, 2023 at 4:23 PM Les wrote:
{
>
> "Trigger Name": "RI_ConstraintTrigger_a_75463",
>
> "Constraint Name": "fk_pfq_src_product_file",
>
> "Relation": "product_file",
>
> "Time": 11179.429,
>
> "Calls": 90
>
> },
>
...
> The one with fk_pfft_product looks like this, it has about
On Fri, Aug 4, 2023 at 11:00 AM Dane Foster wrote:
> Hello,
>
> I'm trying to understand a bit of weirdness in a plan output. There is a
> sort node above a sequential scan node where the scan node produces 26,026
> rows yet the sort node above it produces 42,995,408. How is it possible
> to
On Thu, Jun 1, 2023 at 4:51 AM James Pang (chaolpan)
wrote:
> 2) from this line, we saw total 42 blocks ,215 chunks
> CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free (215 chunks);
> 7715408 used,
>
> But from sum of it’s child level entrys, total sum(child lines)
> block
On Tue, Mar 7, 2023 at 7:14 AM Praneel Devisetty
wrote:
> Hi,
>
> I have a query which is taking roughly 10mins to complete and the query
> planner is choosing a nested loop.
>
> query and query plan with analyze,verbose,buffers
> qsEn | explain.depesz.com
On Wed, Feb 15, 2023 at 7:13 AM aditya desai wrote:
> Hi,
> We are getting this error when transferring data using COPY command or
> running workflow for huge data. We are using Password Authentication(LDAP)
>
> "Connection forcibly closed remote server"
>
Are you sure that that is the exact
On Mon, Feb 13, 2023 at 10:09 AM Mkrtchyan, Tigran
wrote:
>
> 0.524 0 BEGIN;
> 0.819 0 INSERT INTO t_inodes (inumber, icrtime,
> igeneration)
> 0.962 0 UPDATE t_inodes SET igeneration = igeneration
> + 1 where inumber = :inumber;
>
On Sun, Jan 22, 2023 at 6:34 AM 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.
>
You would need to have an expression index over upper(status) to support
such a
On Tue, Nov 29, 2022 at 4:07 PM David Rowley wrote:
> On Wed, 30 Nov 2022 at 03:31, Tom Lane wrote:
> >
> > Alvaro Herrera writes:
> > > IMO it was a mistake to turn JIT on in the default config, so that's
> one
> > > thing you'll likely want to change.
> >
> > I wouldn't necessarily go quite
On Wed, Jul 27, 2022 at 8:55 AM Rick Otten wrote:
>
> One person I talked to said "try not to have more than 100 partitions",
> even with the latest postgresql you'll end up with a lot of lock contention
> if you go over 100 partitions.
>
>
It is hard to know how seriously to take the advice of
On Fri, Jun 17, 2022 at 1:34 AM James Pang (chaolpan)
wrote:
> Hi ,
>
> We plan to migrate large database from Oracle to Postgres(version 13.6,
> OS Redhat8 Enterprise), we are checking options to make data load in
> Postgres fast. Data volume is about several TB, thousands of indexes,
> many
On Fri, May 27, 2022 at 12:19 PM Alexey Ermakov <
alexey.erma...@dataegret.com> wrote:
> Hello, please look into following example:
>
> postgres=# create table test_array_selectivity as select
> array[id]::int[] as a from generate_series(1, 1000) gs(id);
> SELECT 1000
> postgres=# explain
On Tue, May 31, 2022 at 11:14 AM Praneel Devisetty <
devisettypran...@gmail.com> wrote:
>
> Hi,
>>
>> We are trying to reindex 600k tables in a single database of size 2.7TB
>> using reindexdb utility in a shell script
>> reindexdb -v -d $dbname -h $hostname -U tkcsowner --concurrently -j
>>
On Mon, May 23, 2022 at 3:57 AM Mickael van der Beek <
mickael.van.der.b...@gmail.com> wrote:
> Hello Jeff,
>
> Sadly, the query you suggested won't work because you are only returning
> the first row of the matching inner query rows.
>
Sure, but the query I replaced did the same thing. (I
On Fri, May 20, 2022 at 6:42 AM Mickael van der Beek <
mickael.van.der.b...@gmail.com> wrote:
>
> Query:
>
> EXPLAIN (
>> ANALYZE,
>> VERBOSE,
>> COSTS,
>> BUFFERS,
>> TIMING
>> )
>> SELECT
>> fu.w2_page_idxs
>> FROM
>> fact_users
>> AS fu
>> WHERE
>> EXISTS (
>> SELECT
>>
On Wed, May 4, 2022 at 7:15 PM André Hänsel wrote:
> Quick(?) question... why is there a Sort node after an Index Only Scan?
> Shouldn't the index already spit out sorted tuples?
>
> CREATE INDEX ON orders_test(shipping_date, order_id);
>
> EXPLAIN ANALYZE SELECT
> FROM orders_test
> WHERE TRUE
On Tue, May 3, 2022 at 2:11 PM Levi Aul wrote:
> I have a “temporal table” — a table where there are multiple “versions” of
> entities, with each version having a distinct timestamp:
> CREATE TABLE contract_balance_updates (
> block_id bigint NOT NULL,
> block_signed_at timestamp(0)
On Wed, Apr 27, 2022 at 8:19 AM Mickael van der Beek <
mickael.van.der.b...@gmail.com> wrote:
>
> The last query does not finish after waiting for more than 15 minutes.
> (The temporary view creation is very fast and required due to the same
> query in a CTE greatly reducing performance (by more
On Wed, Apr 20, 2022 at 5:13 AM wrote:
>
> The next thing I did was starting two independent Postgres instances on
> the same server and run independent client applications against each of
> them. This resulted in our application getting almost double of the TPS
> compared to running a single
On Tue, Apr 19, 2022 at 5:00 PM Sbob wrote:
>
> However if we move the file to another server in the same network and
> run with a psql -h then it runs for more than 10min.
What is the ping time? Packet loss? You can't take for granted that the
network is good and fast just because they are
On Fri, Feb 25, 2022 at 3:18 PM Ayub Khan wrote:
> Hi,
>
> Could some some verify the attached query to verify the performance and
> suggest some steps to improve it, this query is created as a view. This
> view is used to get the aggregates of orders based on its current status
>
I don't see
On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh
wrote:
> Hi Team,
>
> Can you please help in tunning the attached query as , i am trying to run
> this query and it runs for several hours and it did not give any output.
>
Several hours is not all that long. Without an EXPLAIN ANALYZE, we could
On Tue, Sep 14, 2021 at 3:55 AM Kristjan Mustkivi
wrote:
> Hello Tomas,
>
> The auto explain analyze caught this:
>
> 2021-09-14 06:55:33 UTC, pid=12345 db=mydb, usr=myuser, client=ip,
> app=PostgreSQL JDBC Driver, line=55 LOG: duration: 5934.165 ms plan:
> Query Text: SELECT * FROM
On Mon, Sep 13, 2021 at 9:25 AM Kristjan Mustkivi
wrote:
>
> I have caught this with AUTOEXPLAIN:
>
> Index Cond: ((cage_code = $3) AND (cage_player_id = $2) AND
> (modified_time < $5))
> Filter: (((product_code)::text = ($1)::text) AND
> ((balance_type)::text = ($4)::text))
On Mon, Sep 13, 2021 at 9:25 AM Kristjan Mustkivi
wrote:
> SELECT
> *
> FROM
> myschema.mytable pbh
> WHERE
> pbh.product_code = $1
> AND pbh.cage_player_id = $2
> AND pbh.cage_code = $3
> AND balance_type = $4
> AND pbh.modified_time < $5
> ORDER BY
>
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
On Fri, Jun 11, 2021 at 12:28 PM Ayub Khan wrote:
> Vijay,
>
> Both tomcat and postgresql are on the same region as that of the database
> server. It is an RDS so I do not have shell access to it.
>
> Jeff,
>
> The tomcat profile is suggesting that it's waiting for a response from the
> database
On Thu, Jun 10, 2021 at 4:06 AM Ayub Khan wrote:
> I did profiling of the application and it seems most of the CPU
> consumption is for executing the stored procedure. Attached is the
> screenshot of the profile
>
That is of your tomcat server? If that is really a profile of your CPU
time
On Tue, Jun 8, 2021 at 12:32 PM Ayub Khan wrote:
> In AWS RDS performance insights the client writes is high and the api
> which receives data on the mobile side is slow during load test.
>
That indicates a client or network problem.
Jeff
On Wed, Jan 6, 2021 at 11:19 AM Don Seiler wrote:
> Good morning,
>
> This week we've noticed that we're starting to see spikes where COMMITs
> are taking much longer than usual. Sometimes, quite a few seconds to
> finish. After a few minutes they disappear but then return seemingly at
> random.
On Tue, Sep 8, 2020 at 9:33 AM aditya desai wrote:
> Hi,
> We have an application where one of the APIs calling queries(attached) is
> spiking the CPU to 100% during load testing.
> However, queries are making use of indexes(Bitmap Index and Bitmap Heap
> scan though).
>
The CPU is there to be
On Tue, Aug 18, 2020 at 8:22 PM Jim Jarvie wrote:
> I've tuned the LIMIT value both up and down. As I move the limit up, the
> problem becomes substantially worse; 300 swamps it and the selects take > 1
> hour to complete; at 600 they just all lock everything up and it stops
> processing. I
On Fri, Aug 14, 2020 at 5:35 PM 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!
>
> Explains and
On Mon, Jul 13, 2020 at 10:23 AM Henrique Montenegro
wrote:
insert into users_no_dups (
> created_ts,
> user_id,
> name,
> url
> ) (
> select
> created_ts,
> user_id,
> name,
> url
> from
> users
> ) on conflict do nothing
>
Once
On Tue, Jun 30, 2020 at 1:02 AM Nikhil Shetty
wrote:
> Hi Bruce,
>
> Based on pg_test_fsync results, should we choose open_datasync or
> fdatasync as wal_sync_method? Can we rely on pg_test_fsync for choosing the
> best wal_sync_method or is there any other way?
>
Probably the default of
On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty
wrote:
> Hi Team,
>
> We have a PostgreSQL 11.5.6 database running on VM.
> RAM - 48GB
> CPU - 6 cores
> Disk - SSD on SAN
>
> We wanted to check how the WAL disk is performing using pg_test_fsync.We
> ran a test and got around 870 ops/sec for
On Sat, May 30, 2020 at 3:37 AM sugnathi hai wrote:
> Hi ,
>
> Can you help to tune the below plan
>
It looks like your query (which you should show us) has something like
ORDER BY modifieddate LIMIT 100
It thinks it can walk the index in order, then stop once it collects 100
qualifying
On Thu, May 7, 2020 at 5:17 PM Avinash Kumar
wrote:
> Hi,
>
> On Thu, May 7, 2020 at 6:08 PM Rory Campbell-Lange <
> r...@campbell-lange.net> wrote:
>
>> One of our clusters has well over 500 databases fronted by pg_bouncer.
>>
>> We get excellent connection "flattening" using pg_bouncer with
>>
On Thu, May 7, 2020 at 4:05 PM samhitha g
wrote:
> Hi experts,
>
> Our application serves multiple tenants. Each tenant has the schema with a
> few hundreds of tables and few functions.
> We have 2000 clients so we have to create 2000 schemas in a single
> database.
>
> While doing this, i
On Wed, May 6, 2020 at 12:20 PM Steve Pritchard
wrote:
> Version: Postgres 9.6.3 production system (but also tested on Postgres 12)
>
> For my query the Planner is sometimes choosing an execution plan that uses
> "Bitmap And" (depending on the parameters):
>
> -> Bitmap Heap Scan on observation
On Fri, Apr 24, 2020 at 2:33 PM Stephen Frost wrote:
> Greetings,
>
> * Jeff Janes (jeff.ja...@gmail.com) wrote:
> > In order to read 1409985 / 12839 = 109 rows per buffer page, the table
> must
> > be extraordinarily well clustered on this index. That degree of
&
On Thu, Apr 23, 2020 at 7:36 AM Arcadio Ortega Reinoso <
arcadio.ort...@gmail.com> wrote:
> explain (analyze, buffers, format text) select * from entidad where
> cod_tabla = 4
>
>
> Index Scan using idx_tabla_entidad on entidad (cost=0.56..51121.41
> rows=1405216 width=20) (actual
On Thu, Feb 27, 2020 at 11:33 AM Ben Snaidero
wrote:
> I have the following query that was on average running in ~2ms suddenly
> jump up to on average ~25ms.
>
What are you averaging over? The plan you show us is slow enough that if
you were averaging over the last 1000 executions, that one
On Wed, Feb 26, 2020 at 1:02 PM Guillaume Cottenceau wrote:
>
> It is actually consistent with using a restored backup on the dev
> computer, as my understanding is this comes out without any
> garbage and like a perfectly vacuumed database.
I think I got that backwards in my previous email.
On Wed, Feb 26, 2020 at 11:17 AM Guillaume Cottenceau wrote:
> Dear all,
>
> I am facing a much, much slower query in production than on my
> development computer using a restored production backup, and I
> don't understand why nor I see what I could do to speedup the
> query on production :/
>
On Tue, Feb 18, 2020, 11:42 PM Nicolas PARIS
wrote:
> However the server has a large amount of ram
> memory and I suspect all of those indexes are already cached in ram.
>
Then there may be no benefit to be had.
>
> I have read that tablespaces introduce overhead of maintenance and
>
On Wed, Feb 5, 2020 at 12:25 PM Arya F wrote:
> If I run the database on a server that has enough ram to load all the
> indexes and tables into ram. And then it would update the index on the HDD
> every x seconds. Would that work to increase performance dramatically?
>
Perhaps. Probably not
On Thu, Jan 9, 2020 at 8:11 PM Marco Colli wrote:
> Hello!
>
> 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
On Fri, Jan 3, 2020 at 9:14 AM Justin Pryzby wrote:
> Is it possible to tell what component of the cost estimate of an index
> scan is
> from the index reads vs heap ?
>
Not that I have found, other than through sprinkling elog statements
throughout the costing code. Which is horrible, because
On Thu, Dec 26, 2019 at 6:57 PM Timothy Garnett
wrote:
>
> So far I've been unable to create a smaller / toy example that exhibits
> the same behavior. Some things that may be unusual about the situation:
> keytbl is bigint and the values are large (all are > 2^48) and sparse/dense
> (big chunks
On Fri, Dec 20, 2019 at 1:58 PM Tom Lane wrote:
> Jeff Janes writes:
> > The docs for parallel_tuple_cost are quite terse, as the reference
> section
> > of the docs usually are:
> > "Sets the planner's estimate of the cost of transferring one tuple from a
> >
The docs for parallel_tuple_cost are quite terse, as the reference section
of the docs usually are:
"Sets the planner's estimate of the cost of transferring one tuple from a
parallel worker process to another process. The default is 0.1."
Usually you can find more extensive discussion of such
On Wed, Dec 18, 2019 at 4:53 AM James(王旭) wrote:
> Hello,
>>
>> I encountered into this kernel message, and I cannot login into the Linux
>> system anymore:
>
>
>>
>> Dec 17 23:01:50 hq-pg kernel: sh (6563): drop_caches: 1
>>
>> Dec 17 23:02:30 hq-pg kernel: INFO: task sync:6573 blocked for more
On Tue, Dec 17, 2019 at 8:08 AM Laurenz Albe
wrote:
> On Mon, 2019-12-16 at 15:50 -0500, Tom Lane wrote:
> > Peter Geoghegan writes:
> > > Why do the first and the twentieth executions of the query have almost
> > > identical "buffers shared/read" numbers? That seems odd.
> >
> > It's repeat
On Wed, Dec 11, 2019 at 5:21 PM Fahiz Mohamed wrote:
> There is a slight different in both instance’s data. Inastanbce 1 contains
> latest data and instance 2 consists of data which is 3 weeks older than
> instance 1.
>
In knowing where to look for differences in performance, there is a big
On Tue, Dec 10, 2019 at 3:40 AM Fahiz Mohamed wrote:
> Thank you very much for your prompt responses.
>
> I have analysed more regarding this and found the long running query.
>
> I ran "explain analyse" on this query and I got following result. (We have
> 2 identical DB instances and they
On Tue, Dec 10, 2019 at 11:43 AM Guillaume Lelarge
wrote:
This query uses the column statistics to estimate bloat. AFAIK, json
> columns don't have statistics, so the estimation can't be relied on (for
> this specific table at least).
>
This was true prior to 9.5 (for xml at least, I don't know
On Fri, Nov 29, 2019 at 11:06 AM Florian Philippon <
florian.philip...@doctolib.com> wrote:
>
> We tried another solution: we loaded a minimal schema (without indexes and
> constraints) on the subscriber and created the subscription. The initial
> copy phase was way faster (a few hours). Then we
On Fri, Dec 6, 2019 at 12:50 PM MichaelDBA wrote:
> And Just to reiterate my own understanding of this...
>
> autovacuum priority is less than a user-initiated request, so issuing a
> manual vacuum (user-initiated request) will not result in being cancelled.
>
Somethings happen in some
On Fri, Dec 6, 2019 at 10:55 AM Mike Schanne wrote:
> The error is not actually showing up very often (I have 8 occurrences from
> 11/29 and none since then). So maybe I should not be concerned about it.
> I suspect we have an I/O bottleneck from other logs (i.e. long checkpoint
> sync times),
On Thu, Dec 5, 2019 at 5:26 PM Mike Schanne wrote:
> Hi,
>
> I am investigating a performance problem in our application and am seeing
> something unexpected in the postgres logs regarding the autovacuum.
>
>
>
> 2019-12-01 13:05:39.029
>
On Mon, Dec 2, 2019 at 8:29 AM Aufar Gilbran wrote:
> Hello,
>
> I'm trying to figure out how to optimise 3-table (many-to-many relation)
> joins
> with predicate, limit, and ordering, where one of the tables returns at
> most one
> row.
>
> This is the query that I have right now:
>
> SELECT
On Sun, Nov 24, 2019 at 1:05 PM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> Hey Jeff,
> This example was only used to show that pg96 had better perfomance than
> pg12 in a very simple case.
>
OK, but do you agree that a 15% slow down is more realistic than 3 fold
one? Or are you
On Sun, Nov 24, 2019 at 8:52 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> Hey Andrew,
> It seems that changing this parameter worked for me.
> Setting it to zero means that there wont be any parallel workers for one
> query right ?
> Is it something familiar this problem with the
On Fri, Nov 15, 2019 at 7:27 PM Craig James wrote:
> On Fri, Nov 15, 2019 at 2:45 PM Jeff Janes wrote:
> BTW, I'll note at this point that "analyze category_staging_8" prior to
> this query made no difference.
>
Isn't that the wrong table to have analyzed? The offend
On Thu, Nov 14, 2019 at 5:20 PM Craig James wrote:
> I'm completely baffled by this problem: I'm doing a delete that joins
> three modest-sized tables, and it gets completely stuck: 100% CPU use
> forever. Here's the query:
>
>
> Aggregate (cost=193.54..193.55 rows=1 width=8)
> -> Nested
On Wed, Nov 13, 2019 at 4:01 PM Luís Roberto Weck <
luisrobe...@siscobra.com.br> wrote:
>
> Maybe PostgreSQL can't find a way to calculate having estimates?
>
I wasn't even thinking of the HAVING estimates I was thinking of just the
raw aggregates. It can't implement the HAVING until has the
On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck <
luisrobe...@siscobra.com.br> wrote:
>
>
> Indeed, reducing the costs made the query run in parallel, but the
> improvement in speed was not worth the cost (CPU).
>
Could you show the plan for that?
On Wed, Nov 13, 2019 at 3:11 PM Luís Roberto Weck <
luisrobe...@siscobra.com.br> wrote:
> Hi!
>
> Is there a reason query 3 can't use parallel workers? Using q1 and q2
> they seem very similar but can use up to 4 workers to run faster:
>
> q1: https://pastebin.com/ufkbSmfB
> q2:
On Wed, Nov 13, 2019 at 6:56 AM Marco Colli wrote:
> > the answer is that is because it is a GIN index. Make the same index
> only as btree, and you should get good performance as it can filter the
> tags within a given project without visiting the table.
>
> Currently I have this GIN index:
>
On Wed, Nov 13, 2019 at 4:20 AM Marco Colli wrote:
> Replying to the previous questions:
> - work_mem = 64MB (there are hundreds of connections)
> - the project 123 has more than 7M records, and those that don't have the
> tag 'en' are 4.8M
>
>
>> What was the plan for the one that took 500ms?
>
>
>
> 3) Here's the query plan that I get after disabling the seq scan:
>
>
> QUERY PLAN
>
>
>
> ---
On Mon, Nov 4, 2019 at 3:38 PM Scott Rankin wrote:
> Definitely no long-running transactions on this table;
>
Any long running transactions at all? The lock on the table is only
necessary to explain why the problem would have gone away at the same time
as the reindex finished. If there is a
On Mon, Nov 4, 2019 at 2:38 PM Scott Rankin wrote:
> Hello all,
>
>
>
> We are trying to debug some slow performance in our production environment
> (Amazon RDS, Postgresql 9.6.11), and we’re looking at a particular EXPLAIN
> node that seems… weird. This is a very large query involving a number
On Wed, Oct 30, 2019 at 12:25 PM Alessandro Baretta
wrote:
> -> Bitmap Index Scan on idx_object
> (cost=0.00..75580.00 rows=1 width=0) (actual time=24.094..24.094
> rows=77 loops=1)
>Index Cond: ((... @> ...::jsonb) AND (...
> @> ...::jsonb))
>
On Mon, Oct 14, 2019 at 1:25 PM dangal wrote:
> Do you think it should increase bgwriter_lru_maxpages due to the value of
> maxwritten_clean?
>
I find the background writer to be pretty unimportant these days. If the
kernel is freely accepting writes without blocking, the backends can
probably
On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby wrote:
> On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote:
> > With seqscan disabled, I get this plan on 9.6:
> > Bitmap Heap Scan on friend (cost=8.42..19.01 rows=14 width=8)
> ...
> > I expected to get an index-only scan in this
On Thu, Oct 10, 2019 at 3:40 AM Ogden Brash wrote:
> If each of the tables has about 3+ billion rows, the index is still going
> to be pretty large and spread over many files. In the source database that
> was backed up, the primary key sequence was sequentially assigned and
> written, but as
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.
>
>
Is this in a probabilistic sense, they take longer on average, or has every
single access gotten slower? If the increase in size
On Wed, Oct 9, 2019 at 4:33 AM Ogden Brash wrote:
> # lsof -p 6600 | wc -l;
> 840
>
> # lsof -p 6601 | wc -l;
> 906
>
> Is that normal? That there be so many open file pointers? ~900 open file
> pointers for each of the processes?
>
I don't think PostgreSQL makes any effort to conserve file
On Wed, Oct 9, 2019 at 10:56 AM Yavuz Selim Sertoğlu (ETIYA) <
yavuz.serto...@etiya.com> wrote:
> Thanks for the reply Tom,
>
> Sorry, I couldn't understand. I just copied inside of view and add
> conditions from query that runs with view.
> The comma parts are the same in two queries, one is
On Wed, Sep 4, 2019 at 1:30 PM Fredrik Blomqvist <
fredrik.blomqvist...@gmail.com> wrote:
> Hi,
>
> I have tried doing some research for quite a while on the performance
> implications of the built-in upsert (INSERT ... ON CONFLICT UPDATE...) when
> a lot of upserts are made. The scale is
On Mon, Aug 26, 2019 at 4:26 AM Barbu Paul - Gheorghe <
barbu.paul.gheor...@gmail.com> wrote:
> On Sun, Aug 25, 2019 at 5:51 PM Jeff Janes wrote:
> >
> > Yes, it certainly looks like it is due to cold caches. But you say it
> is slow at first, and then say it var
On Thu, Aug 22, 2019 at 1:09 AM Pavel Stehule
wrote:
> čt 22. 8. 2019 v 3:11 odesílatel Jeff Janes napsal:
>
>> ...
> But the same advance in v12 which makes it harder to fool with your test
>> case also opens the possibility of fixing your real case.
>>
>
>
1 - 100 of 166 matches
Mail list logo
