Re: pg full text search very slow for Chinese characters

2019-09-10 Thread Michael Lewis
>My postgres instance is based on docker image postgres:11 and runs on my MacBook Pro i7 16GB. How much ram and such did you give to this vm? >To my surprise, postgres 11 is extremely slow when creating a full text index. I added a column of tsvector type and tried to create an index on that col

Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

2019-09-19 Thread Michael Lewis
You can also look at citext type to avoid the casting. customer_keyinteger DEFAULT nextval('customer_key_serial') PRIMARY KEY , cust_no smallint NOT NULL UNIQUE , namevarchar UNIQUE , Why do you have a surrogate primary key g

Re: problems importing from csv

2019-09-19 Thread Michael Lewis
>I am trying to import some data from spreadsheets. Included in the data >sets are US monetary values. These appear in the CSV file, like this: $1.00 >The column is defined like this: NUMERIC(5,2) NOT NULL. 1) remove all $ characters from csv before import OR 2) import into text field (perhaps in

Re: Extend inner join to fetch not yet connected rows also

2019-09-22 Thread Michael Lewis
Just a side note, that the below can be written more simply- CASE WHEN UserSkills.craftsmanship_id IS NULL THEN FALSE ELSE TRUE as has If you want to be direct- UserSkills.craftsmanship_id IS NOT NULL as has

Re: unable to drop index because it does not exists

2019-09-23 Thread Michael Lewis
> > Partition key: LIST (date_part('year'::text, mis_ora)) > As an aside, you may benefit from switching to range partitioning* depending on how your queries are written. If you have conditions such as "WHERE mis_ora BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE" or similar, then the fact that your p

Re: Autovacuum lock conflict

2019-09-23 Thread Michael Lewis
> - For tables that receive only INSERTs, schedule a regular VACUUM > with "cron" or similar. Unfortunately, PostgreSQL isn't very smart > about vacuuming insert-only tables. > What is the need to vacuum on an insert only table? Does that just maintain the freespace map?

Re: managing primary key conflicts while restoring data to table with existing data

2019-09-25 Thread Michael Lewis
> > If the data is held in common tables(bills, vouchers, etc)then the only > thing I see happening is changing the PK values to an unused value. That > could turn into a nightmare though. Not only that you lose the connection > to the original data source. If the data can be broken out into separa

Re: Arrays and ANY problem

2019-09-25 Thread Michael Lewis
> > db=# select name from table_name_ds_tmp where ARRAY[categoryid] = ANY ( > select string_to_array( '200,400', ',')::bigint[] ); > Using either of the below instead, I get the proper result. Why doesn't ANY work? I do not know. select name from table_name_ds_tmp where ARRAY[categoryid] <@ ( sel

Re: Redis 16 times faster than Postgres?

2019-09-29 Thread Michael Paquier
me of this blog post: http://www.databasesoup.com/2015/02/running-with-scissors-mode.html Note that sometimes I have run Postgres on a tmpfs as well to test some specific patches. So that can be done, and of course that's unsafe. -- Michael signature.asc Description: PGP signature

Re: Performance on JSONB select

2019-10-02 Thread Michael Lewis
Much of indexing strategy depends on knowing the data like how many distinct values and what the distribution is like. Is JsonBField->>'status' always set? Are those three values mentioned in this query common or rare? Can you re-write this query to avoid using an OR in the where clause? Are you ju

Re: Query Tuning

2019-10-02 Thread Michael Lewis
Both of the below visualizers can help, but require some knowledge about comparing estimated vs actual row estimates, disk sorts vs in memory, etc. Drawing implications about whether your schema needs to change or just the query will take time to master as well. http://tatiyants.com/pev/#/plans/ne

Re: partitions vs indexes

2019-10-02 Thread Michael Lewis
"I would like to convert a table with a primary key into a partitioned setup by a column which is not part of the primary key" That isn't possible. The partition key must be contained by the primary key. That is, the primary key could be site_id, id and you can create hash partition on id or site_

Re: Performance on JSONB select

2019-10-07 Thread Michael Lewis
> > My Json has always a status > Why declare the "where" clause when creating the index? It would not seem needed if status is always set and so your index will reference all rows in the table. Thanks for sharing the trick of having the second column in the index determine the key based on the f

Re: JSONB maximal length ?

2019-10-07 Thread Michael Lewis
> > Hi >> >>By inserting data in a JSONB type column I got the following error >> message: >> >> *>> * >> >> *ERROR: string too long to represent as jsonb string* >> >> *DETAIL: Due to an implementation restriction, jsonb strings cannot >> exceed 268435455 bytes.* >> >> *<< * >> >> could anyo

Re: Declarative Range Partitioning Postgres 11

2019-10-07 Thread Michael Lewis
No, what you want is not possible and probably won't ever be I would expect. Scanning every partition to validate the primary key isn't scalable.

Re: Declarative Range Partitioning Postgres 11

2019-10-08 Thread Michael Lewis
On Mon, Oct 7, 2019 at 5:56 PM Ron wrote: > On 10/7/19 6:17 PM, Michael Lewis wrote: > > No, what you want is not possible and probably won't ever be I would > expect. > > Sure it is. Maybe not the (weird) way that Postgres does partitioning, > but > the legacy RD

Re: Declarative Range Partitioning Postgres 11

2019-10-08 Thread Michael Lewis
On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan wrote: > Hi Michael, > > > > In this case , I always need to include partition key(date) in primary > key ( if I have a primary key defined on non partition key column e.g id > (in my case), to make it a composite primary

Re: Version 10.7 of postgres

2019-10-09 Thread Michael Paquier
s. I would recommend also that you do not use a version which has known bugs. -- Michael signature.asc Description: PGP signature

Re: SELECT returnig a constant

2019-10-15 Thread Michael Lewis
On Tue, Oct 15, 2019 at 8:25 AM Geoff Winkless wrote: > On Tue, 15 Oct 2019 at 14:35, Ray O'Donnell wrote: > > > > On 15/10/2019 14:28, stan wrote: > > > I used to be able to return a constant value in a SELECT statement in > > > ORACLE. I need to populate a table for testing, and I was going to

Re: Securing records using linux grou permissions

2019-10-15 Thread Michael Lewis
It sounds like you want row level security- https://www.postgresql.org/docs/9.6/ddl-rowsecurity.html But, you will need to define separate roles on the database and ensure that the users and connecting with separate roles. The db can't magically know about the permissions on the OS side. >

Re: Postgres 9.6 active-passive HA cluster

2019-10-16 Thread Michael Lewis
"pg_rewind is a tool for synchronizing a PostgreSQL cluster with another copy of the same cluster, after the clusters' timelines have diverged. A typical scenario is to bring an old master server back online after failover as a standby that follows the new master." -https://www.postgresql.org/docs/

Re: Vacuum very big table - how the full vacuum works in background/internally?

2019-10-17 Thread Michael Lewis
> > We must know it to avoid disk out problems, and too much off-time. > You may be interested in this extension- https://github.com/reorg/pg_repack

Re: drop database

2019-10-17 Thread Michael Lewis
Your plan to loop over tables and truncate them seems great if you are worried. It seems simple to verify that space is being freed as you go, and also easy to change tactics if the need arises. >

Re: Primary key definition?

2019-10-22 Thread Michael Lewis
> > > CREATE TABLE books ( > > id SERIAL PRIMARY KEY, > > > > Which has the advantage of not having to manually create the sequences. > Will > > this also enforce that the "internally created sequence" will be > initialized > > to a value above the maximum key in use on a pg_restore? >

Re: Trying to fetch records only if preceded by at least another one

2019-10-25 Thread Michael Lewis
> > But it prints too many records: all moves cross-multiplied with each other. > > As if I have forgotten to add 1 more condition to the JOIN LATERAL > LIMIT 1 inside your lateral should resolve that. Personally, I'd move that condition to EXISTS condition inside WHERE clause instead. Just a styl

Re: Composite type storage overhead

2019-10-25 Thread Michael Lewis
On Thu, Oct 24, 2019 at 3:35 AM Laiszner Tamás wrote: > Actually, this is not such a unique idea: > https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c > > Thanks for the suggestion to split up the primary key into components. But > even going down this way, packing the compo

Re: Trying to fetch records only if preceded by at least another one

2019-10-25 Thread Michael Lewis
On Fri, Oct 25, 2019 at 2:20 PM Alexander Farber wrote: > Thank you Michael - > > On Fri, Oct 25, 2019 at 7:28 PM Michael Lewis wrote: > >> But it prints too many records: all moves cross-multiplied with each >>> other. >>> >>> As if I have forgotte

Re: PostgreSQL - unrecognized win32 error code: 38

2019-10-26 Thread Michael Paquier
erhaps you are just using a fork of Postgres.. -- Michael signature.asc Description: PGP signature

Re: PostgreSQL - unrecognized win32 error code: 38

2019-10-28 Thread Michael Paquier
rustee" uuid NOT NULL, > "AccessControlType" smallint NULL, > "AceOrder" int NULL > ); The original complain comes from a function called ComputeComputer. What does it do and where does it come from? Does it call any in-core code which would cause the failure. If t

Re: Declarative Range Partitioning Postgres 11

2019-11-01 Thread Michael Lewis
On Fri, Nov 1, 2019 at 9:22 AM Shatamjeev Dewan wrote: > Hi Michael, > > > > I want to create a partition by year and subpartition by month in postgres > 11 timestamp column. Please advise syntax. > https://www.postgresql.org/docs/11/ddl-partitioning.html The document

Re: Are my autovacuum settings too aggressive for this table?

2019-11-01 Thread Michael Lewis
My thinking is opposite from what you have. I consider it important to very aggressive on autovacuum because it only ever does the required amount of work. If a tiny amount of work is needed, it does only that and is done. Assuming it doesn't cause I/O concerns, do it as often as possible to minimi

select view definition from pg_views feature request

2019-11-03 Thread Michael Shapiro
It seems that the definition of a view from pg_catalog.pg_views does not qualify the tables used in the view if the tables are in the current search path. Is it possible to either have the definition always qualify all tables independent of the search_path (or else provide a new column that does t

Re: select view definition from pg_views feature request

2019-11-04 Thread Michael Shapiro
Is that how PgAdmin does it? The views extracted by PgAdmin are fully qualified On Sun, Nov 3, 2019 at 3:15 PM Tom Lane wrote: > Michael Shapiro writes: > > It seems that the definition of a view from pg_catalog.pg_views does not > > qualify the tables used in the view if

Re: select view definition from pg_views feature request

2019-11-05 Thread Michael Shapiro
Can I set search_path='' (ie to a string that does not match any existing schema)? Would that be the proper way to guarantee that the definition for any view will always be fully-qualified? On Sun, Nov 3, 2019 at 3:15 PM Tom Lane wrote: > Michael Shapiro writes: > >

Re: Storing a time interval

2019-11-08 Thread Michael Lewis
You certainly could choose to store as tstzrange, but why not use two fields? https://www.postgresql.org/docs/current/rangetypes.html

Re: Storing a time interval

2019-11-08 Thread Michael Lewis
> CREATE TYPE po_dates AS ( > po_isssued_datetimestamptz, > discount_last_date timestamptz, > net_date timestamptz > ); > What advantage does combining these three values into a custom composite type give you rather than just storing directly?

Re: root page 3 of index "pg_class_oid_index" has level 0, expected 1

2019-11-13 Thread Michael Paquier
tem indexes, you also have the option of using ignore_system_indexes. -- Michael signature.asc Description: PGP signature

Re: naming triggers for execution

2019-11-15 Thread Michael Nolan
> Considering that the auditing needs to be the last, how can I be sure it´ll > ran lastly ? > IMHO, auditing should be done in after- triggers, when that the data in the records being inserted, updated or deleted can't be changed but you can still write to the separate auditing tables. -- Mike No

Re: REINDEX VERBOSE unknown option

2019-11-17 Thread Michael Paquier
ASE | SYSTEM } name -- Michael signature.asc Description: PGP signature

Re: REINDEX VERBOSE unknown option

2019-11-18 Thread Michael Paquier
mar more extensible for future purposes and it eases the option parsing. -- Michael signature.asc Description: PGP signature

Re: Authentication: MD5 to SCRAM-SHA-256 error

2019-11-18 Thread Michael Paquier
r versions of Postgres to be installed in parallel. -- Michael signature.asc Description: PGP signature

Re: ERROR: there is no unique constraint matching given keys for referenced table "audit_p"

2019-11-18 Thread Michael Lewis
On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan wrote: > I am trying to create a foreign key constraint on a table : audit_param in > postgres 12 which references partitioned table audit_p. is there anyway to > get rid of this error. > > > *ERROR: there is no unique constraint matching given

Re: REINDEX VERBOSE unknown option

2019-11-19 Thread Michael Paquier
ncern only the page for REINDEX. Perhaps this could be improved, but I am not sure how and particularly if changing it is worth it as many people are used to the existing way of presenting the commands synopsis as well. -- Michael signature.asc Description: PGP signature

Re: Extract transaction from WAL

2019-11-21 Thread Michael Loftis
On Thu, Nov 21, 2019 at 04:56 Jill Jade wrote: > Hello everyone, > > I am new to Postgres and I have a query. > > I have updated a table which I should not have. > > Is there a way to extract the transactions from the WAL and get back the > previous data? > > Is there a tool that can help to ge

Adding LIMIT changes PostgreSQL plan from good to a bad one

2019-11-21 Thread Michael Korbakov
ay? And what can I do to make the planner pick up a better plan? We are running PostgreSQL 10.10. Sincerely, -- Michael Korbakov

Re: ON COMMIT options for non temporary tables

2019-11-21 Thread Michael Paquier
re simple of course :) -- Michael signature.asc Description: PGP signature

Re: Adding LIMIT changes PostgreSQL plan from good to a bad one

2019-11-22 Thread Michael Korbakov
On November 21, 2019 at 19:14:33, Pavel Stehule (pavel.steh...@gmail.com) wrote: čt 21. 11. 2019 v 17:19 odesílatel Michael Korbakov napsal: > Hi everybody. > > I stumbled upon a weird problem with the query planner. I have a query > on a typical EAV schema: > > SELECT

Re: Adding LIMIT changes PostgreSQL plan from good to a bad one

2019-11-22 Thread Michael Lewis
I try to avoid DISTINCT and use GROUP BY when feasible, as well as avoiding OR condition. If you combined anon1 and anon2 with UNION ALL, and did (inner) join instead of left, or even moved all of that to EXISTS, perhaps that gives you better consistent performance. Something like this- SELECT co

Re: sql query for postgres replication check

2019-11-24 Thread Michael Paquier
hot_standby_delay, as told in https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl -- Michael signature.asc Description: PGP signature

Re: Adding LIMIT changes PostgreSQL plan from good to a bad one

2019-11-25 Thread Michael Korbakov
On November 22, 2019 at 20:28:39, Michael Lewis (mle...@entrata.com) wrote: I try to avoid DISTINCT and use GROUP BY when feasible, as well as avoiding OR condition. If you combined anon1 and anon2 with UNION ALL, and did (inner) join instead of left, or even moved all of that to EXISTS, perhaps

Re: jsonb_set() strictness considered harmful to data

2019-11-27 Thread Michael Paquier
o use Jsonb NULL - "null"::jsonb" > > I don't know, but in this case, the exception should be verbose. This is > "rich" function with lot of functionality @Andrew: This patch is waiting on input from you for a couple of days now. -- Michael signature.asc Description: PGP signature

Re: pg_basebackup + incremental base backups

2019-11-29 Thread Michael Paquier
an, all have implementations to handle the first type of incremental backup. > Is there any alternative? > > Here was a proposal: https://wiki.postgresql.org/wiki/Incremental_backup Yeah, nothing has been done in uptream though in this area. That's more about differential backups.

Re: MS Access Frontend

2019-12-01 Thread Michael Nolan
On Sun, Dec 1, 2019 at 8:09 AM Martin Gainty wrote: > > that said I think MS missed the boat on 2-phase-commits > Microsoft has never really embraced the concept of a multi-user database environment. (It doesn't really understand the concept of a multi-user operating system, either.) -- Mike N

Re: upgrade and migrate

2019-12-03 Thread Michael Paquier
cale pg_dump would be very good at. I would have personally avoided using pg_dump above 10~20GB. Depending on the downtime you are ready to accept, a migration based on Slony could be something to investigate. -- Michael signature.asc Description: PGP signature

Re: upgrade and migrate

2019-12-04 Thread Michael Paquier
On Wed, Dec 04, 2019 at 08:38:01AM +0100, Thomas Kellerer wrote: > But pg_upgrade only supports 8.4+ Ditto. You're right here. -- Michael signature.asc Description: PGP signature

Re: Date created for tables

2019-12-05 Thread Michael Paquier
Postgres has to do it. FWIW, you can track that using an even trigger for CREATE TABLE or other objects which inserts the following in a table of your choice for a given database: - The timestamp of the transaction. - The object name. - Its class ID, say pg_class::regclass for a table, etc. -- Michael signature.asc Description: PGP signature

Re: archiving question

2019-12-05 Thread Michael Paquier
and then do the operation at once without the need to patch Postgres. -- Michael signature.asc Description: PGP signature

Re: Slow planing...

2019-12-06 Thread Michael Lewis
On Fri, Dec 6, 2019, 3:21 AM Mladen Marinović wrote: > Is there a way to detect why the planing is taking this long? > > The database is a 9.6.1 with 32GB of shared_buffers, and 1GB of > maintanance_work_mem, and machine CPU is below 80% all the time. > What is default_statistics_target set to f

Re: Query with correlated join having slow performance

2019-12-09 Thread Michael Lewis
I'd suggest re-writing your query to avoid ORs whenever possible. Is this generated by an ORM or subject to change with filters selected in application or can you totally control it on DB side? It may be hugely more performant to simply rewrite this as (almost) the same query twice UNION ALL'd tog

Re: Counting number of sites with same number of sampling dates

2019-12-11 Thread Michael Lewis
On Wed, Dec 11, 2019 at 1:54 PM Rich Shepard wrote: > A sampling location table has 28 distinct sites, each site being sampled > from 1 to 67 times. I'm trying to obtain the number of sites having 1 > sample, 2 samples, ... 67 samples and am not seeing the solution despite > several alternative q

Re: Race condition while creating a new partition

2019-12-16 Thread Michael Lewis
It looks like you are creating a partition for each minute of the day (when logs get inserted for a given minute at least). Would it be at all reasonable to have an hourly or daily job which creates the partitions ahead of when they are actually needed? If partitions went unused in the recent past,

Re: Partitioned tables and locks

2019-12-19 Thread Michael Lewis
I don't recall the details, but I know v12 included significant enhancements to lock relations later in the process such that when targeting relatively few of the partitions, it can be a major performance boost.

Re: A JOIN question

2019-12-30 Thread Michael Lewis
> > LEFT join mfg_vendor_relationship on > mfg_vendor_relationship.mfg_key = mfg_part.mfg_key > AND > mfg_vendor_relationship.project_key = bom_item.project_key > LEFT join vendor on > mfg_vendor_relationship.vendor_key = vendor.vendor_key > Perhaps I am missing som

Re: How can I set a timeout for a locked table in Function ?

2020-01-03 Thread Michael Lewis
I would not think that behavior would extend to lock_timeout based on the explanation on stackexchange. I would assume that the potentially long runtime in this function is mostly in acquiring the lock and not doing the update given the implied primary key in the where clause, so perhaps lock_timeo

Re: UPDATE many records

2020-01-06 Thread Michael Lewis
> > I’m thinking it might be worth it to do a “quick” test on 1,000 or so > records (or whatever number can run in a minute or so), watching the > processor utilization as it runs. That should give me a better feel for > where the bottlenecks may be, and how long the entire update process would > t

Re: UPDATE many records

2020-01-06 Thread Michael Lewis
On Mon, Jan 6, 2020 at 2:34 PM Mark Zellers wrote: > Just out of curiosity, what kind of trigger are you using, a row level > trigger or a statement level trigger? If you are using a row level > trigger, see if you can achieve your requirements using a statement level > trigger instead. I’m rel

Re: pg_repack failure

2020-01-06 Thread Michael Paquier
compiled by gcc (GCC) > 4.9.3, 64-bit I think that you had better report that directly to the maintainers of the tool here: https://github.com/reorg/pg_repack/ -- Michael signature.asc Description: PGP signature

Re: Upgrade PostgreSQL 9.6 to 10.6

2020-01-08 Thread Michael Lewis
On Wed, Jan 8, 2020 at 7:20 AM github kran wrote: > Sorry for the confusion, I tried to install this extension on 9.6 and it > seems to be not working. Does 9.6 PostGreSQL supports logical replication ? > No. See the top of this page with supported versions listed. Prior to v10, pg_logical exte

Re: what to do after a failover

2020-01-08 Thread Michael Paquier
aker-based stuff just to name two. These rely on more complex architectures, where a third node is present to monitor the others (any sane HA infra ought to do at least that to be honest). -- Michael signature.asc Description: PGP signature

Re: Postgres streaming replication

2020-01-08 Thread Michael Paquier
tial data loss, and the amount of data retention you are willing to have around. There is no way to tell what's good or not for your product if you don't know that first. 2) Read the documentation: https://www.postgresql.org/docs/current/high-availability.html -- Michael signature.asc Description: PGP signature

Re: Upgrade PostgreSQL 9.6 to 10.6

2020-01-08 Thread Michael Lewis
On Wed, Jan 8, 2020 at 8:52 PM github kran wrote: > You are right on RDS but I believe the problem is on Aurora PostgreSQL > where the pglogical throws an error during installation. Are you aware if > this works on Aurora PostGreSQL > It seems like this question should be sent to AWS support fo

Re: partitioned table

2020-01-09 Thread Michael Lewis
> > when I manually mocking the data into both tables are fine and when I run > the procedure, I get errorcode: 42P10 MSG: there is no unique or exclusion > constraint matching on the CONFLICT specification > > the procedure is > ... > INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id, >

Re: what to do after a failover

2020-01-09 Thread Michael Paquier
primary to the original one. Simple is easier to understand. Now the larger your instance, the longer it takes to copy a base backup and the longer your reduce the availability of your cluster. So be careful with what you choose. -- Michael signature.asc Description: PGP signature

Re: pg_stat_statements extension

2020-01-13 Thread Michael Paquier
d with a package different than the main ones for the server and client binaries and libraries. Then, you would most likely look for a package named postgresql-11-contrib or similar. -- Michael signature.asc Description: PGP signature

Re: Multiple Aggregations Order

2020-01-14 Thread Michael Lewis
"handle this aggregated data later in code" What is your end goal though? Also, approx how many rows in these tables? Can you share an example query and plan? What version are you using? >

Re: Fwd: Postgresql Data corruption

2020-01-14 Thread Michael Paquier
;t have a backup, but nothing more as there is no guarantee that other parts of the system are not broken. As others have already pointed out, take the time necessary to read that: https://wiki.postgresql.org/wiki/Corruption But first, you should take three, slow, deep breaths. Rushing can only make things worse. -- Michael signature.asc Description: PGP signature

Re: Problem with SqlState=23505 when inserting rows

2020-01-15 Thread Michael Lewis
On Wed, Jan 15, 2020 at 9:41 AM Werner Kuhnle wrote: > I've tried to using the newer definition: > id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY > but that does not solve the problem. > > Is there a way to define id columns to that when the database provides > values, > it recognizes alread

Re: Can I do this?

2020-01-16 Thread Michael Nolan
On Thu, Jan 16, 2020 at 6:28 AM stan wrote: > I am trying to create a function to automatically create a reference value > when a record is inserted into a table. I want the reference value to > consist of the user that is doing the insert, plus a couple of dates, plus > a sequence number, where

Re: pgbackrest: ERROR: [029]: unable to convert base 10 string '0000000B' to unsigned int

2020-01-21 Thread Michael Paquier
backup would fix this, but it didn't. Did you try to contact the maintainers? Here is the link to the project: https://github.com/pgbackrest/pgbackrest -- Michael signature.asc Description: PGP signature

Re: temporary data after diskspace error

2020-01-27 Thread Michael Lewis
In terms of preventing this happening again, you might consider setting some reasonable temp_file_limit as default. Alternatively or additionally, you can set up another volume and direct temp files to use that to avoid the server crashing if excessive temp files are used in a moment's time. Then o

Re: Jsonb first level keys statistic

2020-01-28 Thread Michael Lewis
Other than creating a functional index on the keys that you most want to access and include that function call in your query, I don't know that you can. Functional indexes get statistics while partial indexes do not. If a GIN index using path operator collected stats on the frequency of the keys, t

Re: Exclude constraint on ranges : commutative containment : allow only complete containment

2020-01-29 Thread Michael Lewis
If the new value does not overlap with any existing, allow. If it does overlap, then it must be fully contained by the existing element, or the existing element must be fully contained by it. Else, reject. Is that right?

Re: POLL: Adding transaction status to default psql prompt

2020-02-05 Thread Michael Paquier
27; when in an aborted > transaction. > > Before making a change to a long-time default, a poll in this group was > requested. Thanks Vik for starting a new thread. For reference here is the thread where the patch is being discussed: https://www.postgresql.org/message-id/09502c40-cfe1

Re: sensible configuration of max_connections

2020-02-07 Thread Michael Lewis
On Fri, Feb 7, 2020 at 6:29 AM Justin wrote: > WorkMem is the biggest consumer of resources lets say its set to 5 megs > per connection at 1000 connections that 5,000 megs that can be allocated. > Clarification- work_mem is used per operation (sort, hash, etc) and could be many many times with

Re: Avoiding out of date statistics / planner

2020-02-12 Thread Michael Lewis
It may also be worth noting that it is possible to make autovacuum/analyze more aggressive, perhaps only on the tables that see large changes in data that might result in a statistics issue. If you could share a query, explain analyze output, and pseudo code or at least description of what sort of

Re: JIT on Windows with Postgres 12.1

2020-02-12 Thread Michael Paquier
rly on Windows though. -- Michael signature.asc Description: PGP signature

Re: JIT on Windows with Postgres 12.1

2020-02-12 Thread Michael Paquier
rted > we'd need to generate all the .bc files we're generating on make based > builds. Oh, I see. That's indeed... Not straight-forward. -- Michael signature.asc Description: PGP signature

Re: pg_upgrade —link does it remove table bloat

2020-02-13 Thread Michael Lewis
There is more than one type of statistics though. Stats on the distribution of data is easily recreated with analyze table_name or analyzing the whole database. What about the stats on how many rows have been inserted or updated since the last (auto)vacuum and that will be used to trigger autovacuu

Re: Error “cache lookup failed for function”

2020-02-20 Thread Michael Lewis
Given the explicit begin before drop/create, this is a bit of an unexpected gotcha to have any impact on other transactions. Are there other known limitations of DDL in a transaction?

Re: Logical replication lag in seconds

2020-02-21 Thread Michael Lewis
I am very interested in this discussion. We settled a table with a single timestamp field that a script updates every minute with NOW() so that we can check the timestamp of that table on the replica, assuming the clocks are synced, then we will be able to compute the lag.

Re: Reset DB stats suggestion pg_stat_reset()

2020-02-24 Thread Michael Lewis
> > We have seen some deadlocks and tempfile count in pg_stat_database view. > We are trying to reset the stats. > Can we use pg_stat_reset() function to reset these stats without any > impact stats of databases. > Please advise the process to reset the stats. > Yes, you could reset stats every mo

Re: Connections dropping while using Postgres backend DB with Ejabberd

2020-02-25 Thread Michael Lewis
work_mem can be used many times per connection given it is per sort, hash, or other operations and as mentioned that can be multiplied if the query is handled with parallel workers. I am guessing the server has 16GB memory total given shared_buffers and effective_cache_size, and a more reasonable w

Re: information_schema performance in Postgres 12

2020-02-26 Thread Michael Lewis
Have you tried with JIT turned off in PG12? The long running node is ProjectSet type which is related to set returning functions. If that is getting evaluated differently in PG12, that may be issue.

Re: V9.5

2020-03-10 Thread Michael Paquier
On Tue, Mar 10, 2020 at 12:23:49PM +0530, Sonam Sharma wrote: > We have pg_read_all_stats role from v9.6. do we have a similar role for > v9.5 and lower versions ? No, and pg_read_all_stats has been introduced in Postgres 10, not 9.6: https://www.postgresql.org/docs/10/release-10.html -- M

Re: Force WAL cleanup on running instance

2020-03-11 Thread Michael Lewis
I don't know the answer to your stated question. I am curious if you have set wal_level = minimal and if not, if that would be appropriate for your use case and might render your concern a non-issue.

Re: Web users as database users?

2020-03-11 Thread Michael Lewis
On Fri, Sep 20, 2019 at 8:19 AM Tom Lane wrote: > There is a restriction on how many distinct GRANTs you can > issue against any one object --- performance will get bad if the ACL > list gets too large. > Any ballpark numbers here? Are we talking 50 or 8000?

Re: vacuum full doubled database size

2020-03-13 Thread Michael Loftis
A vacuum full rebuilds the tables, so yeah if it didn’t successfully complete I would expect a lot of dead data. On Fri, Mar 13, 2020 at 07:41 Zwettler Markus (OIZ) < markus.zwett...@zuerich.ch> wrote: > We did a "vacuum full" on a database which had been interrupted by a > network outage. > > >

Re: Mixed Locales and Upgrading

2020-03-17 Thread Michael Paquier
look at it. Anything ASCII-based should be of no problem. If you have a doubt, reindexing evey index which includes text column data is the best course of action in my opinion if you have any doubts, because that's safe even if it has a higher cost. -- Michael signature.asc Description: PGP signature

Re: Fwd: PG12 autovac issues

2020-03-18 Thread Michael Lewis
Do you have default fillfactor set on this table? If not, I would wonder if reducing it to 50% or even 20% would allow many more HOT updates that would reduce bloat. Also, is there any period of lower activity on your system that you could schedule a vacuum freeze for daily or weekly? I believe ha

Re: Join help, please

2020-03-18 Thread Michael Lewis
work_type.work_type_key = work_type.work_type_key You've done a cross join.

<    1   2   3   4   5   6   7   8   9   >