Re: Define hash partition for certain column values

the same problem. > > If you want to distribute values equally over a fixed number of > partitions, I suggest you partition on a modulo of a monotonously > increasing number (a sequence for example), instead of relying on a random > number. > > > 12.01.2021, 17:55, "M

Re: Accounting for between table correlation

On Fri, Jan 15, 2021 at 10:22 AM Alexander Stoddard < alexander.stodd...@gmail.com> wrote: > The 'fast plans' use parallel seq scans. The 'slow plans' is using index > scans. It appears a good query plan correctly predicts it should be bulk > processing the tables but bad ones get fooled into tras

Re: Accounting for between table correlation

On Fri, Jan 15, 2021 at 9:10 AM Thomas Kellerer wrote: > Maybe there are many "idle in transaction" sessions that prevent > autovacuum from properly > analyzing those tables. Or maybe for some unknown reason autovacuum was > turned off. > Or maybe they receive a lot of bulk loads which would requ

Re: time-based range partitioning and truncate/delete different timezones

What version are you using? How long are you keeping data for? It is possible to partition by hour or would that exceed the number of recommended partitions too quickly for your retention time period? Else, I would partition on date according to the timezone of your data. Selecting from multiple pa

Re: migration from postgres to enterprosedb

You'd probably get more response if you gave more info on your situation and what you have already come up with as a tentative approach- how much data in your 9.5 instance, what OS, what is acceptable downtime, etc. Regardless, do you mean EDB Postgres Advanced Server or just one of the installers

Re: Accounting for between table correlation

On Fri, Jan 15, 2021 at 3:27 PM Alexander Stoddard < alexander.stodd...@gmail.com> wrote: > The queries are complex, multiple joins to 10 plus tables, although most > are to tiny enum type lookup tables. I believe it is the join between the > two large tables that I have described that causes the

Re: upgrade using logical replication

Using pg_upgrade takes minutes for an in place upgrade. If you can allow 1+ hour of downtime, it seems overly complicated to use logical replication.

work_mem, temp_buffers, and temp_file_limit

I've read the documentation but am unclear on what memory is used when temp_buffers are exceeded by temp tables that are created. Does that also go towards temp_file_limit? When are temp files (temp_file_limit) cleared out? If there is a big ugly query that uses 8GB of a 10GB limit, and then anoth

Re: vacuum is time consuming

Assuming that the system is online and in use, you may want to consider doing analyze first to ensure stats are proper, and then vacuum all less-used tables that should be quick then larger/more active tables with the verbose option to see details of why it might be taking time. If you are seeing m

Re: Consequence of changes to CTE's in 12

This functionality seems more a candidate for a set-returning function rather than a view, but I like my views to be side effect free and read only. It would be trivial to implement in plpgsql I believe. If you move the limit 1 to the first CTE, does it not give you the same behavior in both versi

Re: Preventing free space from being reused

What version are you using? What is your usage pattern for insert/update/deletes? If sometimes the JSON data gets too big and the data is moved from in-line storage to TOASTED, then that would be opening up gaps. Or if you are doing deletes. Perhaps adjusting your fillfactor and/or TOAST_TUPLE_TARG

Re: Preventing free space from being reused

If you have no updates or deletes, then I would wonder about setting fillfactor LOWER such that new rows are less likely to find a gap that is acceptable. Depending how/when you use the json, lowering toast_tuple_target may be appropriate to store (nearly?) all out of line and making the data store

Re: Turn jit off for slow subquery in Postgres 12

Either turn it off, or increase jit_above_cost, jit_inline_above_cost, and/or jit_optimize_above_cost.

Re: Slow index creation

What is your concern with it taking 20 hours vs 1 hour? Is this index re-created on a regular basis? Would it make any sense to materialize the value of foo(a,b,c) as a generated column (PG12+ natively, or maintained by a trigger before)? Or even bar(foo(a,b,c),geom)? Do you know if parallel_work

Re: How to return a jsonb list of lists (with integers)

Aggregate functions work on a single column to summarize many rows into fewer rows. You seem to be wanting to combine multiple columns which would be done by concatenation or array[column1,column2] or something like that.

Re: Slow index creation

> > https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/ Thanks for this reference. I enjoy your blog, but haven't made the time to read all the archives somehow. Stuff doesn't stick very well when it isn't yet "needed" info besides. I have seen overhead from 'raise notice' in

Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

Your explain analyze is showing an example that runs in less than 15ms, and your logs indicate queries taking 2-3 seconds. I am missing part of the picture here. Can you clarify?

Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

Using auto_explain may be needed to catch the plan when it is slow, if the other suggestions don't resolve things for you.

Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

No issues for us. We have used a low sample rate of 1% or so and gotten some very useful data. Particularly with logging nested statements so we can profile stored procs more easily than adding a large number of raise notice statements.

Re: Simple IN vs IN values performace

Wouldn't using “= any(array)” change how the query is planned? Or is the concern just parsing the values?

Re: Simple IN vs IN values performace

> > Insert the values into a TEMPORARY TABLE, then join that to your main > table? > In my experience, this is very performant but needs an analyze command after populating the temp table to ensure there are statistics so the plan doesn't go awry. Otherwise, I'm not sure it is different from a mat

Re: Batch update million records in prd DB

Of course it will impact a system using that table, but not significant I expect and the production system should handle it. If you are committing like this, then you can kill the script at any time and not lose any work. The query to find the next IDs to update is probably the slowest part of this

Re: Server hangs on pg_repack

Why not use reindex concurrently?

Re: Batch update million records in prd DB

It might be a concern, but generally that should be a row level lock and only block other update/delete options on those rows. It might be helpful to look at the explain analyze output early on vs later in the process. It might be that you are getting very few hot updates and indexes are being upda

Re: Batch update million records in prd DB

1) Don't pretend it is a left join when your where clause will turn it into an INNER join. LEFT JOIN pol gp ON gab.policy_id = gp.id WHERE *AND gp.name LIKE 'Mobile backup%' AND gp.deleted_at IS NOT NULL;* 2) It is interesting to me that the ro

Re: Duplicate key error

It sounds like this should be re-written as 'insert on conflict do update' statement.

Serializable read only deferrable- implications

real life differences between the two commands below? Would this be applicable at all to sessions on a physical replica? SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; *Michael Lewis | Databas

Re: Serializable read only deferrable- implications

> > A transaction started with the first statement will not take any SI locks, > nor > will it ever receive a serialization error. > What is the meaning of SI? Anything you are aware of in source code or a blog post that discusses this?

Re: Serializable read only deferrable- implications

On Tue, Mar 8, 2022 at 9:27 AM Adrian Klaver wrote: > "PostgreSQL maintains this guarantee even when providing the strictest > level of transaction isolation through the use of an innovative > Serializable Snapshot Isolation (SSI) level." > > Then: > > > https://www.postgresql.org/docs/current/tr

Re: foreign key on delete cascade order?

> > We are updating the entire status table every 5 minutes with > BEGIN; > UPDATE status SET () WHERE pollid = $1; > COMMIT; > > The issue is arriving when some does a DELETE during the UPDATE of status > DELETE FROM mach WHERE machid=$1; > Could you set lock_timeout, lock table explicitly for SH

Re: delete query using CTE

You can't delete rows that are in a CTE. You want to delete rows that are in the table. Do you have a primary key that you can reference? Else, you may need to reference the system column ctid.

Re: Indexes that would span partitions.

How many partitions do you expect to have? Why are you partitioning on that column? Do you have need to drop old data all at the same time? How many rows in each partition or in total do you expect to have?

Re: Indexes in JSONB

No matter what you do, you will not typically get the same level of performance as normalized tables as you do not get statistics on the values stored in your jsonb column. No knowledge of null fraction, number of distinct values, or most common values and their frequencies.

Re: Serializable read only deferrable- implications

Sorry for the confusion I caused. The question about connection management and pg bouncer was a distraction and should have been addressed separately. When having a mixture of OLTP and OLAP on the same primary databases, is there any benefit to declaring long running report type connections as SER

LwLocks contention

We are occasionally seeing heavy CPU contention with hundreds of processes active but waiting on a lightweight lock - usually lock manager or buffer mapping it seems. This is happening with VMs configured with about 64 CPUs, 350GBs ram, and while we would typically only have 30-100 concurrent proce

Re: LwLocks contention

On Thu, Apr 21, 2022 at 6:17 AM Chris Bisnett wrote: > We're working to update our application so that we can > take advantage of the pruning. Are you also using native partitioning? > No partitioned tables at all, but we do have 1800 tables and some very complex functions, some trigger insanity

Re: Is this a reasonable use for advisory locks?

How many of these processes do you expect to have running concurrently? How long does that API call take? Might it be better to update the customer (or in a separate table as suggested) as "catch up charge process started at" and then clear that or set completed time in another column to serialize?

Re: Aurora Postgresql RDS DB Latency

Are default statistics target the same on both prod and AWS? Have you analyzed all tables being used in this query to ensure stats are up proper? If the optimizer is choosing a different plan, then the stats must be different IMO. *Michael Lewis | Software Engineer* *Entrata* On Mon, Feb 11

Temp tables and replication identities

/* ALTER TABLE pg_temp.testing123 REPLICA IDENTITY FULL; */ UPDATE testing123 SET value = 2; *Michael Lewis | Software Engineer* *Entrata* *c: **619.370.8697 <619-370-8697>*

Re: Postgresql RDS DB Latency Chossing Hash join Plan

Did you update the stats by running ANALYZE on the tables involved, or perhaps the entire database on the 'Non prod Aurora RDS instance'? Can you share the two execution plans? *Michael Lewis | Software Engineer* *Entrata* *c: **619.370.8697 <619-370-8697>* On Tue, Feb 12,

Re: Postgresql RDS DB Latency Chossing Hash join Plan

fo_2019_2_part4; analyze asset_info_2019_2_part2; etc? If data are very similar, indexes all exist, and default_statistics_target are the same, then you should be getting the same plans. *Michael Lewis | Software Engineer* *Entrata* *c: **619.370.8697 <619-370-8697>* On Wed, Feb 13, 2019 a

Re: Postgrest over foreign data wrapper

You don't need an fkey to write a select statement with a join. I think I must be missing something. Are you wanting it to enforce integrity across the dblink? Or are you adding an fkey with the assumption that you will get an index? *Michael Lewis | Software Engineer* *Entrat

Re: Postgrest over foreign data wrapper

Ah. I didn't realize Postgrest was something, rather than just a typo. An fkey to a foreign table is not supported. Related- https://dba.stackexchange.com/questions/138591/foreign-key-references-constraint-on-postgresql-foreign-data-wrapper *Michael Lewis | Software Engineer* *Entrat

Re: Postgresql RDS DB Latency Chossing Hash join Plan

/wiki/SlowQueryQuestions *Michael Lewis | Software Engineer* *Entrata* *c: **619.370.8697 <619-370-8697>* On Thu, Feb 14, 2019 at 8:48 AM github kran wrote: > > > On Wed, Feb 13, 2019 at 11:38 AM Michael Lewis wrote: > >> I didn't see your email yesterday, sorry abo

Re: Postgresql RDS DB Latency Chossing Hash join Plan

ld be ignored by normal processes. Glad you got your issue resolved. *Michael Lewis* On Thu, Feb 14, 2019 at 3:11 PM github kran wrote: > > > On Thu, Feb 14, 2019 at 12:43 PM Michael Lewis wrote: > >> How many total rows in these tables? I am assuming these are partitions

Re: Postgresql RDS DB Latency Chossing Hash join Plan

le to test things in an environment that performs significantly different. *Michael Lewis* On Sun, Feb 17, 2019 at 10:01 AM github kran wrote: > > > On Thu, Feb 14, 2019 at 4:58 PM Michael Lewis wrote: > >> This is beyond my expertise except to say that if your storage is SSDs in

Re: Partial index on JSON column

On Tue, Feb 19, 2019 at 1:41 PM Samuel Williams < space.ship.travel...@gmail.com> wrote: > Hello > > I have a table with ~3 billion events. > > Of this, there are a small subset of events which match the following > query: > > CREATE INDEX index_user_event_for_suggestion_notification ON > public.u

Re: Partial index on JSON column

> > On Wed, 20 Feb 2019 at 10:14, Tom Lane wrote: > >> Samuel Williams writes: >> > When I do this query: >> >> > EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->> >> > 'suggestion_id'::text)::integer = 26) AND what = >> 'suggestion_notification'; >> >> > It's slow. I need to expli

Re: idle_in_transaction_session_timeout for a set of SQL statements

On Wed, Feb 27, 2019 at 7:56 AM Jeremy Finzel wrote: > I was hoping to use idle_in_transaction_session_timeout to prevent schema > change migrations from running too long and thereby locking up the > application for an extended period even if any one statement in the > migration is very short. >

Re: Optimizing Database High CPU

> > If those 50-100 connections are all active at once, yes, that is high. > They can easily spend more time fighting each other over LWLocks, > spinlocks, or cachelines rather than doing useful work. This can be > exacerbated when you have multiple sockets rather than all cores in a > single sock

Re: Overloaded && operator from intarray module prevents index usage.

> > Yeah, because it's an exact datatype match while the core operator > is anyarray && anyarray which is not. Can you dumb down how to change the index or column type such that an index will be used for the && operator while intarray extension is installed? We have the intarray extension install

Re: Overloaded && operator from intarray module prevents index usage.

On Thu, Feb 28, 2019 at 3:34 PM Tom Lane wrote: > Michael Lewis writes: > > Can you dumb down how to change the index or column type such that an > index > > will be used for the && operator while intarray extension is installed? > We > > have the intarray ext

Re: Overloaded && operator from intarray module prevents index usage.

On Thu, Feb 28, 2019 at 4:57 PM Ron wrote: > On 2/28/19 4:53 PM, Michael Lewis wrote: > [snip] > > Would a sixth option be to re-create the column as array type > > > Codd is spinning in his grave... > I'd hope he would be fine with people asking questions to le

Re: Overloaded && operator from intarray module prevents index usage.

> > Arrays are -- by definition -- not atomic, and so they fundamentally break > the model that relational databases are founded upon. If you want to be a > good database designer, don't use arrays. > Thanks. I was reading about Codd after your last email, but couldn't guess at which point was ob

Re: Overloaded && operator from intarray module prevents index usage.

I'll try to stay off your lawn. >

Re: Postgres 10 and auto vacuum

> > *autovacuum_analyze_threshold* > *autovacuum_analyze_scale_factor* > Changing these will impact how often the table is analyzed based on the rough count of changed rows. You may want to adjust autovacuum settings as well so that dead space can be reused. > *default_statistics_target* > Increa

Re: Conditional INSERT

> > On Fri, Mar 15, 2019 at 10:55 AM basti > wrote: > >> Hello, >> >> I want to insert data into table only if condition is true. >> For example: >> >> INSERT into mytable (domainid, hostname, txtdata) >> VALUES (100,'_acme.challenge.example', 'somedata'); >> > Alternative to a trigger implemen

Re: Recommendation to run vacuum FULL in parallel

"Sometimes a table's usage pattern involves much more updates than inserts, which gradually uses more and more unused space that is never used again by postgres, and plain autovacuuming doesn't return it to the OS." Can you expound on that? I thought that was exactly what autovacuum did for old ve

Re: Forcing index usage

> > Is there a way to tell Postgres “please don’t use index X when queries > that could use index Y instead occur?” > No. But you could re-write the query to make the date index useless. The simplest way that comes to mind is putting the query that does your full-text search in a CTE (WITH keyword

Re: Forcing index usage

Thanks for that advance warning since it is a handy option to force the planning barrier in my experience. What's a resource to see other coming changes in v12 especially changes to default behavior like this? Will there be a new cte_collapse_limit setting or similar?

Re: When do vacuumed pages/tuples become available for reuse?

> > vacuum frees tuples just fine. It's just that by the time each run > finishes many more accumulate due to table update activity, ad nauseum. So > this unused space constantly grows. Here's a sample autovacuum run: > > 2019-04-11 19:39:44.450841500 [] LOG: automatic vacuum of table > "foo.publi

Re: When do vacuumed pages/tuples become available for reuse?

> > > 2019-04-11 19:39:44.450844500 tuples: 19150 removed, 2725811 remain, > 465 are dead but not yet removable > > What Jeff said. This vacuum spent a lot of time, only to remove miserly > 19k tuples, but 2.7M dead tuples remained ... probably because you have > long-running transactions preven

Re: When do vacuumed pages/tuples become available for reuse?

> > Way to many indexes. I'm going to have a hard time convincing our > programmers to get rid of any of them ) > You can create (concurrently) an identical index with a new name, then drop old version concurrently and repeat for each. It doesn't help you figure out the root cause and how to preve

Re: Possible corrupt index?

Which version? What are the queries you are running which give unexpected behavior? Have your run explain analyze on those to check what plan is being used? Have your reindexed all or only the one you suspect? >

Re: Forcing index usage

> > > * Michael Lewis (mle...@entrata.com) wrote: > > > Thanks for that advance warning since it is a handy option to force the > > > planning barrier in my experience. What's a resource to see other > coming > > > changes in v12 especially changes

Re: Method to pass data between queries in a multi-statement transaction

> > Thus, what I'm looking for here is way to store the information and then > pass that information to the next query efficiently. > For example, is it possible to define a struct of my choice, private to > the current transaction, that would store the data and then pass it around > to the next qu

Re: analyze causes query planner to choose suboptimal plan for a select query in separate transaction

I assume it is in the documentation, but I am not aware of how stats are handled for uncommitted work. Obviously in the example you provided the table would be empty, but in your real tests do they start out empty? Would it suffice to use temp tables created like the regular ones and analyze after

Re: analyze causes query planner to choose suboptimal plan for a select query in separate transaction

On Thu, Apr 25, 2019, 11:34 AM Martin Kováčik wrote: > Turning off autovacuum for the tests is a valid option and I will > definitely do this as a workaround. Each test pretty much starts with empty > schema and data for it is generated during the run and rolled back at the > end. I have a lot of

Re: Migrating an application with Oracle temporary tables

*I did find a scenario where this approach does run into trouble. That is, if the function/procedure is executed against the permanent table and then you go to run it against a temporary table. In that case, I do get the wrong answer, and I haven't yet figured out how to reset that without droppi

Re: Bulk inserts into two (related) tables

For each row- Insert into organizations table if the record does not exist, returning ID. Insert into people using that ID. Else, load all the data with empty ID column on person table,then just update the person table afterward and drop the org name column. Perhaps I am missing something.

Re: Feature request (or at least discussion): enable autovaccum on temp tables

> > So, a related question, since we have dozens of temp tables and a lot of > code, is there a way to look up what temp tables are being created by the > current session, so I can do a VACUUM or ANALYZE on all of them in bulk? I > know I can inspect pg_temp_* schema, but how to figure out which on

Re: json on child table or not

How big does the data stored in that field get? More than 2KB? Real question- is it getting stored plain, compressed inline, or toasted? Have you set the storage strategy/type, or is it the "extended" default behavior that compresses and then stores in the toast table if still more than 2000 bytes?

Re: Research on ?? operators

*":foo" named placeholders* If I may, is this supported natively in Postgres prepared statements? Can I see an example? I do not care much for the numbers of positional placeholders and would love to use names instead if possible.

Re: perf tuning for 28 cores and 252GB RAM

> > If your entire database can comfortably fit in RAM, I would make > shared_buffers large enough to hold the entire database. If not, I would > set the value small (say, 8GB) and let the OS do the heavy lifting of > deciding what to keep in cache. If you go with the first option, you > probably

Re: PostgreSQL 11 Auto vacuum

> > Actually we have notice that Auto vacuum in PG10 keeps vacuuming the > master tables and that takes a lot of time and Don't go the child table to > remove the dead tuples. > What do the logs say actually got done during these long running autovacuums? Is it feasible to increase the work allo

Re: Duplicate key error

Why just do a plain update, relying on row level locking to serialize requests properly, and then just do an insert where not exists? Is there value in doing the delete? I don't see it. Note- On conflict clause is supported from 9.5+ and that is already past EOL. Upgrading to at least v10 is recom

Re: Duplicate key error

I just meant a regular update (which might impact 0 rows) and then insert (not exists) like you are doing already. --transaction still ways. Should manual locking used or is there better method. I don't follow what you mean.

Re: Question about when PostgreSQL 11.0 was released

I can't find a page on the website right now that specifies it, but the feature freeze is around April each year then general availability in October. Minor releases are scheduled quarterly I believe. Major versions are EOL'd after 5 or so years.

Re: MultiXactId wraparound and last aggressive vacuum time

I don't believe you can determine a date/time that it happened, but querying the age of the table is simple to do and then you can compare that with the freeze age parameters. A periodic manual vacuum freeze may preclude the system ever needing to perform the emergency autovacuum freeze, and as an

Re: Primary keys and composite unique keys(basic question)

Etiquette on these lists is to reply in line or below the relevant portion, not top-post with full quoting like default gmail behavior. On Wed, Mar 31, 2021 at 9:18 AM Mohan Radhakrishnan < radhakrishnan.mo...@gmail.com> wrote: > But we don't search using UUIDs always. Only when data from another

Re: Slick way to update multiple tables.

You can have an updatable view.

Re: Slick way to update multiple tables.

Joins are allowed though.

Re: Slick way to update multiple tables.

postgresql.org/docs/current/sql-createview.html My apologies. It seems INSTEAD OF triggers are required to implement updates across multiple tables. I thought not if all were simple joins. My mistake.

Re: Timestamp/hstore query?

If you share example schema and desired output (like a dummy table or even pseudo code SQL), then I'm sure many people could help you. Right now, the description of your desired result seems a bit unclear, at least to me. If you wanted to run this hourly for the last 1 hour, it sounds a bit like

Re: Suboptimal plan when IN(...), ORDER BY and LIMIT are used (no JOINs)

Your query and explain analyze output do not seem to match. Filter: (cred_id = '1001344096118566254'::bigint) I don't see anything like that in your query, nor an index that would support accomplishing that without filtering after fetching the 184k rows initially like the planner does. >

Re: BRIN index on timestamptz

On Fri, Apr 23, 2021, 2:32 AM Mohan Radhakrishnan < radhakrishnan.mo...@gmail.com> wrote: > I execute this on the AWS RDS instance. Is there something in the plan I > should pay attention to ? I notice the Execution Time. > > > " -> Bitmap Index Scan on "testtab_date_brin_idx " (cost=0.00..20.0

Re: BRIN index on timestamptz

On Sat, Apr 24, 2021, 1:27 AM Mohan Radhakrishnan < radhakrishnan.mo...@gmail.com> wrote: > What's your question exactly? If you have confidence that correlation will > remain high (insert only table, or occasional cluster/repack with cluster > is done), then BRIN can be a good fit. If you do upda

Re: index only scan taking longer to run

Without knowledge of the structure of your data, or even real table/field names to be able to read the query with some sense of what the tables might be like, offering advice is rather difficult. I didn't notice any nodes with huge estimation problems at least. I will suggest that DISTINCT is ofte

Re: trigger impacting insertion of records

It seems like there is some flaw here. From my reading, on insert of any row, you are updating ALL rows in the same table to just remove an underscore if it matches the pattern of 'US_' at the beginning. That doesn't seem likely to be what you want. I'd think you would want something like the below

Re: ON CONFLICT DO NOTHING ignored on bulk insert

Are you wanting to minimize the locking time, or ensure the whole process completes as soon as possible? If the prior, you can insert into a temp table like the real one (which is maybe what pricelistnew is already), delete the rows where they don't have a valid reference, and then insert all remai

Re: AWS forcing PG upgrade from v9.6 a disaster

On Sat, May 29, 2021, 4:40 AM Lionel Bouton wrote: > The last time I had to use this setting to solve this kind of problem I > ended with : > > default_statistics_target = 500 > > But obviously the value suited to your case could be different (I'd > increase it until the planner uses the correct

Re: [Extern] Re: autovacuum on pg_catalog tables

Why not change the defaults? How many tables would hit this new threshold and you would NOT want autovacuum to process them? >

Re: Use case stuck due to Partitioning

Do you have an index on the "updated_at" field and found that the query is too slow? Do you have an example query?

Re: Use case stuck due to Partitioning

I am unclear exactly what you want to do with modified_date. Can you write pseudo code perhaps? It sounds like you are wanting to union all the results of the query you gave, with the results of a similar query where modified_date >= '2021-03-01 08:16:13.589' and created_at < '2021-03-01 08:16:13.

Re: Do table-level CHECK constraints affect the query optimizer?

Are vacuum and analyze happening regularly on the live system? What's an example query that uses indexes on test and does not on live? Does the live system show poor estimates when executing 'explain analyze select...' and the test system show semi-accurate row estimates? 50 million seems to be a

Re: Do table-level CHECK constraints affect the query optimizer?

Other than rows being frozen on test (and not on live), I'm not aware of anything that would push the planner to choose to do an index scan on an entire table. Maybe someone else will chime in. Or, if you try running vacuum freeze on live and can verify if that changes the result. I'm not sure why

Re: EXPLAIN with anonymous DO block?

It sounds like you are wanting to run 'explain analyze [query]' inside a loop inside a DO block. That isn't possible as far as I know, but auto_explain and log_nested_statements should be able to let you profile the whole thing and perhaps you can pick out the part you want from the logs.

Re: On partitioning, PKs and FKs

Why are you using hash partitioning?

Re: optimization issue

((current_setting('env.groupid'::text))::integer)::numeric If you pass this value in directly as part of the query string, how does it perform? It seems like calling the function to get this value may be creating a planning problem with the value unknown at plan time. If you were able to put the r

Re: Frequetly updated partial index leads to bloat on index for Postresql 11

Have you tried setting autovacuum to run quite aggressively, perhaps just on this table? Have you tried an index on the status column only, rather than partial?

<    1   2   3   4   >