Re: Advice needed: query performance deteriorates by 2000% within 1 minute

If you expect to have high cache hits and/or have ssd or similar fast storage, random page cost should be more like 1-2 rather than the default 4. When using jsonb, you'd normally have estimates based solely on the constants for the associated datatype (1/3 or 2/3 for a nullable boolean for instanc

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

And I would absolutely crank up autovacuum and analyze settings. Turn up the cost limits, turn down the cost delays, decrease the scale factor. Whatever you need to do such that autovacuum runs often. No need to schedule a manual vacuum at all. Just don't wait until 20% of the table is dead before

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

What are these two tables partitioned by? On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar Hi, > > Thanks, i know if explicitly we give in where condition it is working. > > i thought with below parameter in Postgresq11 this issue is fixed ? > > * enable_partitionwise_join to 'on';* > > * what is th

Re: autovacuum big table taking hours and sometimes seconds

It is curious to me that the tuples remaining count varies so wildly. Is this expected? *Michael Lewis* On Thu, Feb 14, 2019 at 9:09 AM Mariel Cherkassky < mariel.cherkas...@gmail.com> wrote: > I checked in the logs when the autovacuum vacuum my big toasted table > during the week

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

Yeah, the planner doesn't know that call_created_date can be limited on both tables unless you tell it specify it in the where condition as Laurenz said on another thread. *Michael Lewis* On Thu, Feb 14, 2019 at 7:35 AM suganthi Sekar wrote: > Both table Portion by sam

Re: autovacuum big table taking hours and sometimes seconds

Thanks, that context is very enlightening. Do you manually vacuum after doing the big purge of old session data? Is bloat causing issues for you? Why is it a concern that autovacuum's behavior varies? *Michael Lewis* On Thu, Feb 14, 2019 at 12:41 PM Mariel Cherkassky < mariel

Re: autovacuum big table taking hours and sometimes seconds

s you proposed, it seems likely to be good to significantly increase autovacuum_vacuum_cost_limit on this table, and perhaps decrease autovacuum_vacuum_scale_factor if it is not being picked up as a candidate for vacuum very frequently. *Michael Lewis * On Thu, Feb 14, 2019 at 1:08 PM Mariel Cherk

Re: index on jsonb col with 2D array inside the json

or. I would not expect that you could index (unnest data->>'ranges' for instance) to get the separated out range values. *Michael Lewis * On Tue, Feb 19, 2019 at 8:59 AM Mariel Cherkassky < mariel.cherkas...@gmail.com> wrote: > Hi, > I have a table with

Re: index on jsonb col with 2D array inside the json

st on range vs btree on two int columns. It seems a little odd to have a jsonb value to hold multiple range values. A range is already a complex type so separating out into the association table like R3 would make sense to me. *Michael Lewis* On Tue, Feb 19, 2019 at 9:34 AM Mariel Cherk

Re: Why isn't an index scan being used?

On Tue, Feb 19, 2019, 8:00 PM Andrew Gierth > "Abi" == Abi Noda writes: > > Abi> However, when I index the closed column, a bitmap scan is used > Abi> instead of an index scan, with slightly slower performance. Why > Abi> isn't an index scan being used, given that the exact same number >

Re: Aggregate and many LEFT JOIN

? *Michael Lewis* On Fri, Feb 22, 2019 at 8:54 AM Justin Pryzby wrote: > On Fri, Feb 22, 2019 at 04:14:05PM +0100, kimaidou wrote: > > Explain shows that the GROUP AGGREGATE and needed sort kill the > performance. > > Do you have any hint how to optimize this ? > > http

Re: Aggregate and many LEFT JOIN

s going to make the critical difference. Unless it could fetch the data sorted via an index, but that doesn't seem likely. I would suggest increase default_statistics_target, but you have good estimates already for the most part. Hopefully someone else will chime in with more. *Michael Lewis*

Re: Aggregate and many LEFT JOIN

On Mon, Feb 25, 2019 at 2:44 AM kimaidou wrote: > I have better results with this version. Basically, I run a first query > only made for aggregation, and then do a JOIN to get other needed data. > > * SQL : http://paste.debian.net/1070007/ > * EXPLAIN: https://explain.depesz.com/s/D0l > > Not re

Re: Query slow for new participants

> Indexes: > "pk_pricedb" PRIMARY KEY, btree (companyid, articleid) > "EnabledIndex" btree (enabled) > "ix_companyarticledb_article" btree (articleid) > "ix_companyarticledb_company" btree (companyid) > I'd say drop ix_companyarticledb_company since pk_pricedb can be used instead e

Re: Shared_buffers

On Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe wrote: > Daulat Ram wrote: > > I want to know about the working and importance of shared_buffers in > Postgresql? > > is it similar to the oracle database buffer cache? > > Yes, exactly. > > The main difference is that PostgreSQL uses buffered I/O, wh

Re: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction

For kicks I tried the example given and got the below which seems more expected. explain analyze select * from brin_test where id >= 9; Bitmap Heap Scan on brin_test (cost=5.78..627.36 rows=9861 width=8) (actual time=0.373..7.309 rows=10001 loops=1) Recheck Cond: (id >= 9) Rows Remo

Re: EXPLAIN ANALYZE of BRIN bitmap index scan with disjunction

I ran both many times and got the same result. ::shrug::

Re: Optimizing `WHERE x IN` query

Did you create a GIN index on subscriptions column to support the && operator?

Re: Searching in varchar column having 100M records

On Fri, Jul 19, 2019 at 8:13 AM mayank rupareliya wrote: > Well, you haven't shown us the execution plan, so it's hard to check why > it did not help much and give you further advice. > > > This is the latest query execution with explain after adding indexing on > both columns. > > Aggregate (co

Re: High concurrency same row (inventory)

Can you share the schema of the table(s) involved and an example or two of the updates being executed?

Re: Planner performance in partitions

"It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with *up to a few hundred partitions fairly well*, provided that typical queries allow the query planner to prune all but a small n

Re: Planner performance in partitions

Thanks for clarifying your position and sharing the results you have seen. That is impressive indeed. It seems likely that waiting for v12 is needed since feature are not back patched. Perhaps one of the contributors will confirm, but that is my expectation.

Re: Last event per user

The obfuscation makes it difficult to guess at the query you are writing and the schema you are using. Can you provide any additional information without revealing sensitive info? 1) Do you have an index on ( user_id ASC, timestamp_inc DESC ) ? 2) Sub-queries can't be re-written inline by the opti

Re: Last event per user

It seems like it should be- SELECT * FROM users u JOIN last_user_event_1 e USING (user_id,user_group); --OR-- SELECT * FROM last_user_event_2 e; for them to produce the same result set, since the last_user_event_2 already (could) have users info in it very simply by select * instead of e.* in that

Re: Planner performance in partitions

Was there a reason to exceed 100-500 partitions in real life that pushed you to do this test? Is there some issue you see when using 100 partitions that is solved or reduced in severity by increasing to 1200 or 6000 partitions?

Re:

If you modify last_user_event_2 to select user and event info in the view, and just put there where clause directly on the view which is not joined to anything, instead of on the "extra copy" of the users table like you were showing previously, I would expect that the performance should be excellen

Re: Last event per user

On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck < luisrobe...@siscobra.com.br> wrote: > If you modify last_user_event_2 to select user and event info in the view, > and just put there where clause directly on the view which is not joined to > anything, instead of on the "extra copy" of the users

Re: Extremely slow HashAggregate in simple UNION query

I believe this would be relevant- https://www.cybertec-postgresql.com/en/optimizer-support-functions/ It seems there is hope down the road to improve those estimates.

Re: Extremely slow count (simple query, with index)

> > You can SELECT reltuples FROM pg_class WHERE oid='subscriptions'::oid, but > its > accuracy depends on frequency of vacuum (and if a large delete/insert > happened > since the most recent vacuum/analyze). > This only seems helpful to find approx. count for the entire table, without considering

Re: select distinct runs slow on pg 10.6

If you can't modify the query, then there is nothing more to be done to optimize the execution afaik. Distinct is much slower than group by in scenarios like this with many columns. You already identified the disk sort and increased work mem to get it faster by 3x. There are not any other tricks of

Re: pg12 - migrate tables to partitions structure

How many rows are you dealing with currently? What are your queries like? Have you looked at doing a hash partition on product.id? Is this on a test system or destined for a production environment in the near future? I ask because PG12 is still in beta.

Re: pg12 - migrate tables to partitions structure

> > All the queries uses the vendor product and thats why this column is a > perfect fit as a partition column. > My main table is big (10M+) (Product), but other tables can also be > big(1M+).. > I assume you have query performance problems and are hoping partitioning will help? Are you read heav

Re: pg12 - migrate tables to partitions structure

Is this being done because it can be, or is it solving a real-life pain point? Just wondering what the perspective is here. Much of partitioning strategy seems to me to revolve around how the system is used, and not just the schema and what is possible. For instance, you can mimic primary and fore

Re: Slow query on a one-tuple table

Is this result able to be repeated?

Re: Slow query on a one-tuple table

> > I have about 6 bigint fields in this table that are very frequently > updated, but none of these are indexed. I thought that by not having an > index on them, would make all updates HOT, therefore not bloating the > primary key index. Seems I was wrong? > HOT update is only possible if there i

Re: pg12 - partition by column that might have null values

Just create a partial index on id column where end_date = infinity (or null if you really prefer that pattern) and the system can quickly find the rows that are still most current revision. How many rows do you have in this table? Or foresee ever having? What took you down the road of partitioning

Re: Query went slow all of sudden. ON V 11.3

What are approx row counts and distribution of data in the concerned tables and columns? Have you run EXPLAIN (query plan) to get the plan that will be executed and can you paste on https://explain.depesz.com/ and share the link that results? Do you have an index on LOWER( cr ) on table temp_10032

Re: distinct on extract returns composite type

As long as we are on the performance list and not general, it might be worth noting that partitioning should be defined directly on the data and not on a function result I believe. If you always do the extract year and extract quarter thing, it may work out just fine. But just a regular btree index

Re: Query slows when used with view

> > Those are not equivalent queries. Read up on the syntax of FROM; > particularly, that JOIN binds more tightly than comma. > I see this- "A JOIN clause combines two FROM items, which for convenience we will refer to as “tables”, though in reality they can be any type of FROM item. Use parenth

Re: Query slows when used with view

> > When you join to a view, the view sticks together, as if they were all in > parentheses. But when you substitute the text of a view into another > query, then they are all on the same level and can be parsed differently. > > Consider the difference between "1+1 * 3", and "(1+1) * 3" > I thou

Re: Query slow again after adding an `OR` operation (was: Slow PostgreSQL 10.6 query)

Are you prefixing this auto generated query with set join_collapse_limit = 30, or are you changing the default and reloading config? That is, can you impact ONLY this query with these config changes? I wouldn't assume so, so any hack/query hint like turning off hashjoins (which seem to be chosen in

Reading explain plans- row estimates/actuals on lower nodes vs next level up

30 actual and 3350 vs 3320)... why does the higher node have such a different estimate vs actual ratio? *Michael Lewis | Software Engineer* *Entrata*

Re: Slow planning, fast execution for particular 3-table query

Is default_statistics_target set above default 100? I would assume that would reflect in the size of pg_statistic, but wanted to ask since increasing that from 100 to 1000 was the only time I have seen planning time explode. Are other queries slow to plan?

Re: Slow "not in array" operation

What's the plan for the slow one? What's the time to just count all rows? >

Re: Slow "not in array" operation

It is very interesting to me that the optimizer chose a parallel sequential scan rather than an index scan on either of your indexes that start with project_id that also reference trashed_at. 1) Are you running on SSD type storage? Has random_page_cost been lowered to 1-1.5 or so (close to 1 assum

Re: Slow "not in array" operation

h=0) (actual time=1493.823..1493.823 rows=7203173 loops=1) > >Index Cond: (project_id = 123) > > Planning Time: 1.273 ms > > Execution Time: 95132.766 ms > > (15 rows) > > > On Tue, Nov 12, 2019 at 8:20 PM Michael Lewis wrote: > >>

Re: Simple DELETE on modest-size table runs 100% CPU forever

> If I leave out the "analyze", here's what I get (note that the > categories_staging_N table's name changes every time; it's > created on demand as "create table categories_staging_n(id integer)"). > How/when are they created? In the same statement? After create, are you analyzing these tables? I

Re: Re[4]: Postgresql planning time too high

As a matter of habit, I put all inner joins that may limit the result set as the first joins, then the left joins that have where conditions on them. I am not sure whether the optimizer sees that only those tables are needed to determine which rows will be in the end result and automatically priori

Re: Make recently inserted/updated records available in the buffer/cache

"I am going to use it as a queue" You may want to look at lowering fillfactor if this queue is going to have frequent updates, and also make autovacuum/analyze much more aggressive assuming many updates and deletes.

Re: Make recently inserted/updated records available in the buffer/cache

On Tue, Dec 3, 2019 at 11:46 AM Sachin Divekar wrote: > I am also going to use SKIP LOCKED to _select for update_. Any suggestions > on tuning parameters for SKIP LOCKED? > I am not aware of any. Either you use it because it fits your need, or not. Note- please don't top-post (reply and include

Re: autovacuum locking question

On Thu, Dec 5, 2019 at 3:26 PM Mike Schanne wrote: > I am concerned that if the autovacuum is constantly canceled, then the > table never gets cleaned and its performance will continue to degrade over > time. Is it expected for the vacuum to be canceled by an insert in this > way? > > > > We are

Re: Specific query taking time to process

> > There is a specific search query I am running to get list of Documents and > their metadata from several table in the DB. > We are running Postgres 9.6.9 on Amazon RDS (db.m5.4xlarge instance) > > Our current DB consists of 500GB of data and indexes. Most of the rows in > table are consist of 4

Re: Specific query taking time to process

On Mon, Dec 9, 2019 at 3:39 PM Fahiz Mohamed wrote: > I ran "explain analyse" on this query and I got following result. (We have > 2 identical DB instances and they consist of same data. Instane 1 took 20+ > second to process and instance 2 took less than a second) > > Instance 1: (This is used b

Re: Specific query taking time to process

This seems beyond me at this point, but I am curious if you also vacuumed alf_node_properties and alf_node tables and checked when they last got (auto)vacuumed/analyzed. With default configs for autovacuum parameters and tables with that many rows, they don't qualify for autovacuum very often. I do

Re: Seeking reason behind performance gain in 12 with HashAggregate

I am not at all familiar with PostGIS so perhaps this is a silly question, is bloat an issue on the older instance? Correlation isn't causation, but half the buffers scanned and half the runtime in the v12 plan has me curious why that might be. >

Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters

Does the behavior change with different values of Ver column? I'd be curious of the fraction in the MCVs frequency list in stats indicates that rows with Ver = 92 are rare and therefore the index on only Ver column is sufficient to find the rows quickly. What is reltuples for this table by the way?

Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters

base. I ran the query for > all of them. The only one miss-behaving is "92". I ran the query with > random values for Ver (invalid values), the query plan always attempts to > use the index using both values. > I looked into "most_common_values" in pg_stats, this val

Re: Query optimization advice for beginners

You've got two references to order_basketitemdetail both aliased to bid and ALSO a table called order_basketitembatch aliased to bib. I assume that confuses the planner, but even if it doesn't it certainly confuses any new developers trying to understand the query's intention. The biggest thing th

Re: How to avoid UPDATE performance degradation in a transaction

If your trigger is supposed to change certain fields, you could return OLD instead of NEW if those fields have not been changed by the trigger. You could also check an updated_on timestamp field to verify if the row has already been modified and potentially skip the trigger altogether. Just a coupl

Re: much slower query in production

By the way, I expect the time is cut in half while heap fetches stays similar because the index is now in OS cache on the second run and didn't need to be fetched from disk. Definitely need to check on vacuuming as Justin says. If you have a fairly active system, you would need to run this query ma

Re: much slower query in production

> > UPDATE multicards >SET defacements = COALESCE( count, 0 ) > FROM ( SELECT multicard_uid, COUNT(*) AS count FROM tickets GROUP BY > multicard_uid ) AS sub > WHERE uid = multicard_uid OR multicard_uid is null; > I expect this should work. Not sure of performance of course.

Re: Many DataFileRead - IO waits

How big is ix_tabledata_intid_timestampdate_intotherid3_intotherid2 on disk? If you create another index with same fields, how much space does it take? Real question- are you vacuuming aggressively enough for your workload? Your index name seems to indicate that intotherid3 would be the third key,

Re: Many DataFileRead - IO waits

If no updates or deletes are happening on the table, it would be best practice to set up a scheduled manual vacuum analyze to ensure statistics and the visibility map is updated. Other than creating the index on the first two columns only, I'm out of ideas. Hopefully someone running Postgres at lar

Re: JOIN on partitions is very slow

Are you able to tweak the query or is that generated by an ORM? What version of Postgres? Which configs have you changed from default? How many partitions do you have? Is there an index on company name? Anytime I see distinct keyword, I expect it to be a performance bottleneck and wonder about rew

Re: JOIN on partitions is very slow

On Mon, Mar 23, 2020 at 1:40 AM daya airody wrote: > 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 o

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

If you can afford the time, I am not sure the reason for the question. Just run it and be done with it, yes? A couple of thoughts- 1) That is a big big transaction if you are doing all the cleanup in a single function call. Will this be a production system that is still online for this archiving?

Re: PostgreSQL does not choose my indexes well

> > "unless the table is mostly marked all-visible" Is that taken into account during planning when evaluating index scan vs sequential scan?

Re: The query plan get all columns but I'm using only one column.

The example is nonsensical so I expect it is too contrived to be useful for analyzing the actual problem. Additionally, the total query time is under 1ms and most of it is planning time. Use a prepared statement or do something else to reduce planning time like reducing statistics target if that a

Re: The query plan get all columns but I'm using only one column.

It is generally a very bad idea to turn off autovacuum. When it is causing problems, it is likely that it needs to run more often to keep up with the work, rather than not run at all. Certainly if it is turned off, it would be critical to have a regularly scheduled process to vacuum analyze all tab

Re: The query plan get all columns but I'm using only one column.

> > In staging environment we have disabled autovacuum since that it is a > testing environment and the database are restored very often. > But in production environment it is enabled autovacuum=on > > The weird case is that production was slow and staging environment was > faster. > You haven't s

Re: 600 million rows of data. Bad hardware or need partitioning?

What kinds of storage (ssd or old 5400 rpm)? What else is this machine running? What configs have been customized such as work_mem or random_page_cost?

Re: Duplicate WHERE condition changes performance and plan

Why not vacuum analyze both tables to ensure stats are up to date? Have you customized default_statistics_target from 100? It may be that 250 would give you a more complete sample of the table without increasing the size of the stats tables too much such that planning time increases hugely. Do yo

Re: good book or any other resources for Postgresql

I don't know the others, but have enjoyed and learned a great deal from The Art of PostgreSQL. >

Re: AutoVacuum and growing transaction XID's

It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any settings changed from default related to autovacuum? https://www.postgresql.org/docs/9.6/routine-vacuuming.html Read 24.1.5. Preventing Transaction ID Wraparound Failures These may also be of help- https://info.crunchydata

Re: AutoVacuum and growing transaction XID's

autovacuum_naptime being only 5 seconds seems too frequent. A lock_timeout might be 1-5 seconds depending on your system. Usually, DDL can fail and wait a little time rather than lock the table for minutes and have all reads back up behind the DDL. Given you have autovacuum_vacuum_cost_limit set t

Re: Request to help on GIS Query improvement suggestion.

Your indexes and operators are not compatible. You have added a btree index on md5 function result and are not using md5 in your query, and also using LIKE operator not one of the supported ones. I believe it might use a btree operator (plain value, not md5 result) if you are always searching for "

Re: view reading information_schema is slow in PostgreSQL 12

On Fri, Jun 12, 2020 at 12:26 PM regrog wrote: > I'm facing performance issues migrating from postgres 10 to 12 (also from > 11 > to 12) even with a new DB. > Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12. > > I have a view that abstracts the data in the database: > > CREAT

Re: simple query running for ever

On Tue, Jun 16, 2020 at 2:35 PM Nagaraj Raj wrote: > I wrote a simple query, and it is taking too long, not sure what is wrong > in it, even its not giving EXPLAIN ANALYZE. > More context is needed. Please review- https://wiki.postgresql.org/wiki/Slow_Query_Questions

Re: Unclamped row estimates whith OR-ed subplans

> > While you're waiting, you might think about recasting the query to > avoid the OR. Perhaps you could do a UNION of two scans of the > transactions table? > Minor note- use UNION ALL to avoid the dedupe work if you already know those will be distinct sets, or having duplicates is fine.

Re: Sudden insert performance degradation

Is this an insert only table and perhaps not being picked up by autovacuum? If so, try a manual "vacuum analyze" before/after each batch run perhaps. You don't mention updates, but also have been adjusting fillfactor so I am not not sure.

Re: Hstore index for full text search

Hash Cond: (o.courier_id = cc.id) Filter: (((o.tracker_code)::text ~~* '%1654323%'::text) OR ((table_cus.name)::text ~~* '%1654323%'::text) OR ((au.username)::text ~~ '%1654323%'::text) OR ((o.source)::text ~~* '%1654323%'::text) OR ((o.ops -> 'shop'::text) ~~* '%1654323%'::text) OR ((o.ops -> 'cam

Re: Hstore index for full text search

On Tue, Aug 11, 2020 at 4:46 PM Tom Lane wrote: > A GIN index on an hstore column only provides the ability to search for > exact matches to hstore key strings. There are a few bells and whistles, > like the ability to AND or OR such conditions. But basically it's just an > exact-match engine,

Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

Message queue... Are rows deleted? Are they updated once or many times? Have you adjusted fillfactor on table or indexes? How many rows in the table currently or on average? Is there any ordering to which rows you update? It seems likely that one of the experts/code contributors will chime in and

Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

On Tue, Aug 18, 2020 at 6:22 PM Jim Jarvie wrote: > There is some ordering on the select [ ORDER BY q_id] so each block of 250 > is sequential-ish queue items; I just need them more or less in the order > they were queued so as near FIFO as possible without being totally strict > on absolute sequ

Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

Also, have you checked how bloated your indexes are getting? Do you run default autovacuum settings? Did you update to the new default 2ms cost delay value? With a destructive queue, it would be very important to ensure autovacuum is keeping up with the churn. Share your basic table structure and i

Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

Great to hear that some of the issues are now mitigated. Though, perhaps you actually require that ORDER BY if items are expected to be sitting in the queue quite some time because you have incoming queue items in a burst pattern and have to play catch up sometimes. If so, I highly suspect the inde

Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

Can you share an explain analyze for the query that does the select for update? I wouldn't assume that partition pruning is possible at all with hash, and it would be interesting to see how it is finding those rows. >

Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

On Thu, Aug 20, 2020 at 4:40 PM Jim Jarvie wrote: > On 20-Aug.-2020 17:42, Michael Lewis wrote: > > Can you share an explain analyze for the query that does the select for > update? I wouldn't assume that partition pruning is possible at all with > hash, and it would be inter

Re: Too few rows expected by Planner on partitioned tables

On Wed, Aug 26, 2020, 1:37 AM Julian Wolf wrote: > Hi Justin, > > thank you very much for your help and sorry for the late answer. > > After testing around with your suggestions, it actually was the daterange > type which caused all the problems. Messing around with the statistics > value improve

Re: Query performance issue

CREATE INDEX receiving_item_delivered_received ON receiving_item_delivered_received USING btree ( eventtype, replenishmenttype, serial_no, eventtime DESC ); > More work_mem as Tomas suggests, but also, the above index should find the candidate rows by the first two keys, and then be able to skip t

Re: Query performance issue

Note- you may need to vacuum* the table to get full benefit of index only scan by updating the visibility map. I think index only scan is skipped in favor of just checking visibility when the visibility map is stale. *NOT full

Re: Query performance issue

"Subquery Scan on rec (cost=1628601.89..1676580.92 rows=7381 width=41) (actual time=22171.986..23549.079 rows=1236042 loops=1)" " Filter: (rec.mpos = 1)" " Rows Removed by Filter: 228737" " Buffers: shared hit=45 read=1166951" " I/O Timings: read=29.530" " -> WindowAgg (cost=1628601.89..1658127.45

Re: Query performance issue

On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj wrote: > Hi Mechel, > > I added the index as you suggested and the planner going through the > bitmap index scan,heap and the new planner is, > HaOx | explain.depesz.com > > HaOx | explain.depesz.com > >

Re: Performance Issue (Not using Index when joining two tables).

Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND f_sel_policy_all(vpd_key, 'CH R_EMP_POSITION'::character varying) AND f_sel_policy_prod_locale((cep.*)::character varying, prod_locale_code)) This looks like some stuff for row level security perhaps. My understanding is

Re: Single column vs composite partial index

Index Cond: ((load_dttm)::date >= (CURRENT_DATE - 7))" There is no need to cast the load_dttm field to a date in the query. The plain index on the field would be usable if you skipped that. In your example, you show creating the single column index but it isn't getting used because of the type cas

Re: Too many waits on extension of relation

What is relation 266775 of database 196511? Is it cms_c207c1e2_0ce7_422c_aafb_77d43f61e563.cms_item or some system catalog table? When I search google for "ExclusiveLock on extension of relation" I find one thread about shared_buffers being very high but not big enough to fit the entire data in th

Re: Slow Query

Is there no index on thread.spool? What about notification.user? How about message.time (without thread as a leading column). Those would all seem very significant. Your row counts are very low to have a query perform so badly. Work_mem could probably be increased above 4MB, but it isn't hurting th

Re: Slow Query

Based on the execution plan, it looks like the part that takes 13 seconds of the total 14.4 seconds is just calculating the max time used in the where clause. Anytime I see an OR involved in a plan gone off the rails, I always always check if re-writing the query some other way may be faster. How's

Re: CPU Consuming query. Sequential scan despite indexing.

What version by the way? Do you get a faster execution if you disable sequential scan? Or set parallel workers per gather to 0? Your estimates look decent as do cache hits, so other than caching data or upgrading hardware, not sure what else there is to be done. Although... you are hitting 70k blo

Re: CPU Consuming query. Sequential scan despite indexing.

Reply to the group, not just me please. Btw, when you do reply to the group, it is best practice on these lists to reply in-line and not just reply on top with all prior messages quoted. On Sun, Oct 18, 2020 at 3:23 AM aditya desai wrote: > I tried vacuum full and execution time came down to hal

  1   2   >