Re: Use case stuck due to Partitioning

[Michael Lewis]

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

[Michael Lewis]

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.589'.

However, if modified date is not null, and is set to the created_date by
default, then there seems no reason to check the created_date except if
partition pruning at plan time is very important.

Are you experiencing an actual performance problem, are you unsure how to
write the query for the data you want, or is this premature optimization?

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

[Michael Lewis]

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 fairly low row count to be partitioned. What
version is this on?

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

[Michael Lewis]

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 sampling rate would matter at all if you are reading the
entire set of data.

What version?

>

Re: EXPLAIN with anonymous DO block?

[Michael Lewis]

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

[Michael Lewis]

Why are you using hash partitioning?

Re: optimization issue

[Michael Lewis]

((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 result on an analyzed temp table at least, I'd expect
that the planner would have enough info to make good decisions.

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

[Michael Lewis]

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?

Re: Partitioning a table by integer value (preferably in place)

[Michael Lewis]

On Fri, Aug 13, 2021 at 3:02 AM Vijaykumar Jain <
vijaykumarjain.git...@gmail.com> wrote:

> ... use binary split for large partitions, to avoid large row movements.
>

Would you expound on this?

Re: Using a TRIGGER with window functions.

[Michael Lewis]

If you want to update many rows after updating N rows, you want an after
STATEMENT trigger which is executed after insert, update or delete. You
also want to ensure that only that function maintains sort_order field and
that you don't update recursively, perhaps by executing that function when
NEW.sort_order IS NOT DISTINCT FROM old.sort_order to prevent an infinite
loop, and executing a different function when NEW.sort_order IS DISTINCT
FROM OLD.sort_order such that you ensure all other fields have not changed.

By the way, your window function could be row_number() - 1 instead of
activity_id - FIRST_VALUE(activity_id).

Re: Regexp_replace bug / does not terminate on long strings

[Michael Lewis]

150/25,150/15,150/6,200/15,200/20,200/10,200/40,200/50,200/7,200/25,200/200,240/15,250/20,250/50,250/10,250/25,250/250,300/20,300/30,300/7,300/300,300/10,300/50,400/20,400/25,450/50,500/500,500/50,500/35,500/25,600/40,1000/20,1000/40,1000/1000,1000/35,1000/50,1000/500

*/


If you don't need the order maintained, it becomes a much simpler problem
and you can strip off some of this complexity.

*Michael Lewis  |  Database Engineer*
*Entrata*


On Thu, Aug 19, 2021 at 4:42 PM Tom Lane  wrote:

> "Markhof, Ingolf"  writes:
> > BRIEF:
> > regexp_replace(source,pattern,replacement,flags) needs very (!) long to
> > complete or does not complete at all (?!) for big input strings (a few k
> > characters). (Oracle SQL completes the same in a few ms)
>
> Regexps containing backrefs are inherently hard --- every engine has
> strengths and weaknesses.  I doubt it'd be hard to find cases where
> our engine is orders of magnitude faster than Oracle's; but you've
> hit on a case where the opposite is true.
>
> The core of the problem is that it's hard to tell how much of the
> string could be matched by the (,\1)* subpattern.  In principle, *all*
> of the remaining string could be, if it were N repetitions of the
> initial word.  Or it could be N-1 repetitions followed by one other
> word, and so on.  The difficulty is that since our engine guarantees
> to find the longest feasible match, it tries these options from
> longest to shortest.  Usually the actual match (if any) will be pretty
> short, so that you have O(N) wasted work per word, making the runtime
> at least O(N^2).
>
> I think your best bet is to not try to eliminate multiple duplicates
> at a time.  Get rid of one dup at a time, say by
>  str := regexp_replace(str, '([^,]+)(,\1)?($|,)', '\1\3', 'g');
> and repeat till the string doesn't get any shorter.
>
> I did come across a performance bug [1] while poking at this, but
> alas fixing it doesn't move the needle very much for this example.
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/message-id/1808998.1629412269%40sss.pgh.pa.us
>
>
>

Re: Regexp_replace bug / does not terminate on long strings

[Michael Lewis]

Btw- My apologies for top posting. I think my caffeine wore off.

Re: string_agg distinct order by

[Michael Lewis]

I believe that you could define an enumerated type to use for those status
colors such that the ordering is defined as you like without two separate
columns for the name and sort_value or whatever.


https://www.postgresql.org/docs/current/datatype-enum.html

Example in the documentation expanded a little to demonstrate-

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
INSERT INTO person VALUES ('Joe', 'sad');
INSERT INTO person VALUES ('Roe', 'ok');

SELECT * FROM person order by current_mood;
SELECT * FROM person order by current_mood desc;

Note- using enum may complicate other things in your usage, so I am not
suggesting this is ideal, just one option.


*Michael Lewis  |  Database Engineer*
*Entrata*

Re: Querying a table with jaccard similarity with 1.6 million records take 12 seconds

[Michael Lewis]

This is showing many false positives from the index scan that get removed
when the actual values are examined. With such a long search parameter,
that does not seem surprising. I would expect a search on "raj nagar
ghaziabad 201017" or something like that to yield far fewer results from
the index scan. I don't know GIN indexes super well, but I would guess that
including words that are very common will yield false positives that get
filtered out later.

>

Re: Query takes around 15 to 20 min over 20Lakh rows

[Michael Lewis]

What is T and how many rows are in there? How many rows in task_history?
What indexes exist? Are you confident you want 2 million rows in that
result set? What version is this on? What pg_settings have been changed
from defaults?

Re: Query takes around 15 to 20 min over 20Lakh rows

[Michael Lewis]

Have you ever used this site to visualize the explain plan and spot bad
estimates and slow nodes? https://explain.depesz.com/s/WE1R

This stands out to me-

*Subquery Scan on npiactionjoin (cost=10,165,289.40..10,167,192.01 rows=293
width=16) (actual time=118,413.432..118,806.684 rows=446,782
loops=1)Filter: (npiactionjoin.rn = 1)*

It seems that estimate is pretty far off and this node and the final node
above this are the biggest slowdowns. If you filtered down to the record
you want from task_history BEFORE the join, then maybe you would have
quicker results. I might try a materialized CTE or even an analyzed temp
table if that option is available to you, so the planner makes informed
decisions.

By the way, the order by on that row_number seems like you are getting the
OLDEST activity related to the task which could maybe be cached rather than
re-calculated daily as this query runs.


*Michael Lewis  |  Database Engineer*
*Entrata*

Re: Fastest option to transfer db?

[Michael Lewis]

What version of Postgres is the source? Can you make use of logical
replication?

Re: Azure Postgresql High connection establishment time

[Michael Lewis]

This is not a Postgres issue. Please reach out to the Azure team.

>

Re: Question about behavior of conditional indexes

[Michael Lewis]

You likely need to tune your autovacuum settings such that the index bloat
reaches a steady-state and does not continue to increase. When vacuum runs,
it will remove dead (and no longer visible to any running transactions)
tuples aka row versions from each page (8KB block by default) in the file
for that table's data. It will also update the index, except in newer
versions of Postgres where that behavior becomes optional (see manual for
which version and whether it still runs by default). If you are I/O bound
and cannot tune the system defaults to autovacuum more proactively (when a
smaller percent of rows are dead), then perhaps just change the settings
for that table as it seems to be functioning a bit like a queue. Or you
might consider setting up a daily job to vacuum analyze on all tables, if
there is a period of low system activity. If you didn't have the index on
the columns you are updating, then reducing fillfactor would be an option
to increase HOT updates and thereby prevent the index bloat. Alas, that's
not an option with the index needing to reference those values that are
being changed.

"index002" btree (action_performed, should_still_perform_action,
action_performed_at DESC) WHERE should_still_perform_action = false
AND action_performed = true

That index seems odd to me. Why index a single value for the first two
columns? I would recreate it with those skipped. Also, based on the
names, I'd expect the opposite for true and false. That is, the
"interesting" rows are where the action has NOT yet been performed yet
and it is needed. I'd expect the index as defined to cover most of the
table rather than a small fraction. Perhaps just a typo from manual
obfuscation.

For what it's worth, you can create new concurrently, drop old
concurrently, then rename new to old. That would be the same result as a
reindex concurrently.

>

Re: Timestamp with vs without time zone.

[Michael Lewis]

Related to this current discussion and exchange of ideas... is there a best
practice for retrieving data in such a way as the rows are localized to a
timezone for where/group by purposes. That is, if I have a table which has
events, but those events belong to a tenant or some entity that has a
location which implies a timezone (or at least an offset), is there a best
way to write a query similar to the below? Please forgive and overlook if
there is some obvious syntax error, as this is just a quick and dirty
example. Might it make sense to store a "localized" version of the
timestamp *without* timezone on the event record such that an index can be
used for fast retrieval and even grouping?

select
date_trunc( 'month', e.event_datetime AT TIMEZONE t.time_zone_name ) AS
event_date,
count( e.id )
from events AS e
join tenants AS t ON t.id = e.tenant_id
where e.event_datetime AT TIMEZONE t.time_zone_name >= '01/01/2021'::DATE
AND e.event_datetime AT TIMEZONE t.time_zone_name < '09/01/2021'::DATE;

Re: Question about behavior of conditional indexes

[Michael Lewis]

Just to clarify, I suggested fillfactor likely could *not* help because you
are indexing a column that has the value change. The idea with reducing
fillfactor is that the rows can be updated with the new versions remaining
in the same 8KB block in the file for table/row storage. If the new version
of the row remains in the same page, then the index pointer doesn't have to
be updated until that old version of the row gets vacuumed away. But alas,
when the value in the index changes, then all bets are off. Although, I
suppose in your workflow you might update these rows frequently and NOT
change the status column, then I would certainly consider reducing the
fillfactor, but it will mean perpetual "reserved space" (normally called
bloat though that has a different implication) in the blocks/pages that
only hold old records that won't be getting updates anymore.

If you check pg_stat_user_tables, then you will see autovauum count and can
check it periodically to see how often it is doing work. I'd lower
autovacuum_vacuum_scale_factor and perhaps autovacuum_vacuum_cost_delay
(default was 20ms and lowered to 2ms in PG12) to ensure work is done more
often, and more work is done in each cycle before it pauses to avoid
overloading the I/O system.

>

Re: Timestamp with vs without time zone.

[Michael Lewis]

On Wed, Sep 22, 2021 at 12:44 AM cen  wrote:

> On 21. 09. 21 23:34, Michael Lewis wrote:
>
> Related to this current discussion and exchange of ideas... is there a
> best practice for retrieving data in such a way as the rows are localized
> to a timezone for where/group by purposes. That is, if I have a table which
> has events, but those events belong to a tenant or some entity that has a
> location which implies a timezone (or at least an offset), is there a best
> way to write a query similar to the below? Please forgive and overlook if
> there is some obvious syntax error, as this is just a quick and dirty
> example. Might it make sense to store a "localized" version of the
> timestamp *without* timezone on the event record such that an index can be
> used for fast retrieval and even grouping?
>
> select
> date_trunc( 'month', e.event_datetime AT TIMEZONE t.time_zone_name ) AS
> event_date,
> count( e.id )
> from events AS e
> join tenants AS t ON t.id = e.tenant_id
> where e.event_datetime AT TIMEZONE t.time_zone_name >= '01/01/2021'::DATE
> AND e.event_datetime AT TIMEZONE t.time_zone_name < '09/01/2021'::DATE;
>
>
> This is an interesting case. A simplified query example would be to "give
> me all events for this year".
>
> I am not sure what the cost of shifting UTC is, probably not much, but
> depending on use case it might make sense to deconstruct into date and time
> for query optimization.
>

Does that solve anything? My concern is that for the events at the begin
and end of the where (or group by) we may want to include them or exclude
them depending on the time in that location. If I want to know how many
events happen between 8am and 5pm for each location in California and New
York for example, I'm not certain if a second timestamp that is "localized"
should be stored, or if there is some better way to achieve that goal. At
least for some data in the evening, the date value will be
different between UTC and local timezone for that location. Just setting my
session to an east coast equivalent timezone when I pull the data still
doesn't give me "what are all the events that happened in California"
accurately. The goal is that the data is accurate for that location. Not as
much a concern if there are only a few hours apart, but if the user is
running a report across properties all over the world... what's the proper
way to ensure each date means the day for that location?

Re: Faster distinct query?

[Michael Lewis]

In the future, please share the plan returned by explain analyze, and some
data about how many rows in the involved tables, what type of system you
are running it on, any changes from default configs, etc.

How does the below work? It should do a very simple index scan only, then
aggregate the relative few rows after the fact.

select station, array_agg(distinct(channel)) as channels
FROM(
SELECT station,channel FROM data GROUP BY station,channel
) AS sub
group by station;

If there is correlation between station & channel, then you might look at
creating a multivariate statistics object and analyzing the table so the
planner can make better choices, knowing that channel is dependent on
station perhaps. I wouldn't necessarily think that it would help this
query, but perhaps others. Also, you might try creating only dependencies,
only ndistinct type, or some combination other than all 3 types.

ref- https://www.postgresql.org/docs/current/sql-createstatistics.html

Re: Faster distinct query?

[Michael Lewis]

On Wed, Sep 22, 2021 at 2:48 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > There is no where clause so I'm doubtful there is much to be gained going
> > down this path.  The Index-Only scan seems like an optimal way to obtain
> > this data and the existing query already does that.
>
> The "index-only" scan is reported to do 86m heap fetches along the
> way to returning 812m rows, so the data is apparently pretty dirty.
>

Do you say that because you would expect many more than 10 tuples per page?

Re: Faster distinct query?

[Michael Lewis]

>
> It’s only when I add in the AND data.channels=channels.channel that the
> query time blows up to 5+ minutes. I personally don’t understand why there
> would be such a large difference between the two queries - something wrong
> with my indexes?
>

Did you do the manual vacuum as suggested by Tom? Maybe at least check
pg_stat_user_tables for the last vacuum or autovacuum timestamp, and how
many records have changed since then. The system is deciding on an index
scan because that should be very fast, but if it needs to look at the
actual table table to determine if the process executing that query should
still be allowed to see that tuple (row version) then the index only scan
slows down a lot I believe. The huge number of heap fetches that Tom
pointed out mean that the system doesn't know that ALL processes should be
able to see those tuples. As someone else suggested lowering the max freeze
age, you might just do a manual "vacuum freeze" as well to allow future
auto vacuum processes to quickly skip over those pages as well.


I've heard of the loose indexscan before mentioned on this thread, but I'm
not seeing how to implement that for multiple columns. Anyone have an
example or pseudo code perhaps?

Re: Using a single sequence for all tables

[Michael Lewis]

If your processes somehow allow updates on the wrong table, then fix that.

If you run out of space in whatever value range you choose initially, the
pain to upgrade to a type that allows larger values would seem to be very
large.

Re: DELETE ... USING LATERAL

[Michael Lewis]

On Mon, Oct 4, 2021, 10:30 AM Nikhil Benesch 
wrote:

> you can't reuse the FROM table name in the USING clause:
>
> # delete from int_arrays using int_arrays;
> ERROR:  table name "int_arrays" specified more than once


> Don't you need to use an alias for the table in the using clause?

Re: Query time related to limit clause

[Michael Lewis]

What's the definition for abc_serv_nch_q1_2021_expr_idx3? That is a jsonb
field I assume? Statistics aren't great on jsonb data, so you may benefit
greatly from pulling keys out to be stored as a standard column. I would be
curious for more "pure" estimates on each quarterly partition directly for
only the condition below (explain analyze, just looking at estimated vs
actual row counts) since they seem to be rather severe overestimates but
I'm not sure how much the date condition is obscuring that.

(((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->>
'subGridName'::text) = '905811-22_MISCN_data'::text)"

The planner is choosing to use ONLY the index on the created timestamp when
limit is 1 and finding they nearly all match (I hope all on the q2
partition) and needs to filter almost all of those out (all from q1
partition I think, and nearly all from the others). I believe that the
planner thinks the other criteria in the query are not nearly as selective
as they are, and so it thinks it will find 1 match very quickly and be
done. That isn't the case.

When you want more rows, the planner decides that using both indexes is
less costly and it is correct.

Re: Avg/max size of these JSON docs in Postgres

[Michael Lewis]

On Tue, Oct 12, 2021 at 1:43 AM Simon Riggs 
wrote:

> On Tue, 12 Oct 2021 at 08:14, Ram Pratap Maurya
>  wrote:
>
> > Confirm what is Avg/max size of these JSON docs in Postgres.
>
> JSON and JSONB datatypes can both be max 1GB in size.
>

That is per row.

Just to confirm- The compression associated with TOAST is only if the value
can fit in line, right? Columns don't get stored out-of-line in a toast
table as a compressed value as I read the documentation. I suppose that
would make reads a bit crazy.

Re: Avg/max size of these JSON docs in Postgres

[Michael Lewis]

On Tue, Oct 12, 2021 at 12:51 PM Simon Riggs 
wrote:

> On Tue, 12 Oct 2021 at 18:53, Michael Lewis  wrote:
> >
> > On Tue, Oct 12, 2021 at 1:43 AM Simon Riggs <
> simon.ri...@enterprisedb.com> wrote:
> >>
> >> On Tue, 12 Oct 2021 at 08:14, Ram Pratap Maurya
> >>  wrote:
> >>
> >> > Confirm what is Avg/max size of these JSON docs in Postgres.
> >>
> >> JSON and JSONB datatypes can both be max 1GB in size.
> >
> > That is per row.
>
> No, that is per column.
>

Yes, sorry. My attempt at clarification only muddled things. Each column
within each row can be up to 1GB in size is how I understand the limit.

Re: Determining if a table really changed in a trigger

[Michael Lewis]

Does this perform differently from suppress_redundant_updates_trigger?

https://www.postgresql.org/docs/current/functions-trigger.html

Re: How to copy rows into same table efficiently

[Michael Lewis]

On Tue, Oct 26, 2021 at 1:07 AM Arun Suresh 
wrote:

> Current approach taken is to build a query like below:
> INSERT INTO mytable (col1, col2, col3, col4) SELECT col1, 'XYZ', col3,
> col4 FROM mytable WHERE col2 = 'ABCD'
>
> Is there a better way to do this?
> There could be other tables with foreign key reference, would a simple
> ordering of the copy based on table relationship suffice?
>

The other concern I would have is having a rolling view of the data in
default read committed mode. If you copy data from a primary table (with
other tables having fkey to that table coming later in the process), then
you may need to use some created_on < script_start_time, else when you try
to insert dependent rows where the record in the primary table did not
exist yet when you started your process and your rows in dependent table
want to reference that primary table row... error.

Or, use REPEATABLE READ, but WAL buildup may be a concern.

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

[Michael Lewis]

>
>  Planning Time: 10.351 ms
>
>  Execution Time: 0.283 ms
>

 Nearly all of the time is in planning... What is your
default_statistics_target?

Re: Determining if a table really changed in a trigger

[Michael Lewis]

If you end up with no rows changing from an insert or delete, something
seems awry. Unless you mean 0 rows affected. Do after statement triggers
still execute? I suppose they very well might.

Would the statement even execute if no rows get updated and that is
prevented with before update? I would assume null is being returned rather
than old if the trigger finds the row to be identical.

Re: Model clause and

[Michael Lewis]

On Thu, Oct 28, 2021 at 1:57 PM SQL Padawan 
wrote:

> I presume that VIRTUAL GENERATED columns are on the to-do list?
>

https://www.postgresql.org/docs/current/ddl-generated-columns.html

Is this not what you want?

Re: Model clause and

[Michael Lewis]

Mea culpa.

"PostgreSQL currently implements only stored generated columns."

I should re-read what I share references to rather than trust my memory,
particularly while sick. Thanks for kindly correcting.

Re: Debugging features needed

[Michael Lewis]

For my purposes, app name isn't long enough so we put a comment at the
start of every SQL that has a unique ID generated in the application. This
ensures that we can tell one connection apart from another even when both
are coming from the same feature/action (applicationName is set to this)
even using connection pooling. Before, we had different requests reusing
the same pid of course and if they happened to have the same appName, we
didn't know it was a new process on app side.

We also log that same request ID to NewRelic and when an app process needs
to use multiple databases, it links those for research of events after the
fact as well.

I would be interested to hear what others are doing.

Re: Logical Replication - Should Destination Table Columns Be Defined With Default Value

[Michael Lewis]

Is there any advantage to not defining the default on the replica? If it is
not a static value and the publishing database will trigger row updates, I
could see waiting to set the default until after the table re-write is
done, but otherwise there doesn't seem to be any benefit to skipping column
defaults on subscribers.

Re: Are all unlogged tables in any case truncated after a server-using

[Michael Lewis]

Why keep them as unlogged tables? If data is static, can you spare the disk
space to gradually copy data from existing unlogged table to new copy that
is logged, and then have brief exclusive lock to drop unlogged and rename
new one?

Re: Are all unlogged tables in any case truncated after a server-crash?

[Michael Lewis]

Curious... why keep the table as unlogged if it is static? If you can spare
the disk space, perhaps just create a regular table with same definition,
gradually copy the data to spread the impact on WAL, and when complete,
just drop the old table and rename the new one.

Row estimate on empty tables

[Michael Lewis]

ref:
https://www.postgresql.org/message-id/9772.1510348781%40sss.pgh.pa.us

This functionality hasn't changed in the years since this post, right? I'm
dealing with temp tables specifically, but seem to be getting bit by this
behavior occasionally so I would like to confirm.


*Michael Lewis  |  Database Engineer*
*Entrata*

Re: Execute command in PL/pgSQL function not executing

[Michael Lewis]

You could also just return the name of the temp table, or return nothing
and directly use that name manually outside the function to do select *
from pg_temp.my_data

Re: [EXTERNAL] Re: Inserts and bad performance

[Michael Lewis]

How many partitions? How many rows do they have when performance is slowing
considerably? Does this table get many updates or is it insert only? What
version of PostgreSQL? Are the inserts randomly distributed among the
partitions or targeting one or a few partitions? Are you able to capture an
example and run it in a transaction with explain (analyze, buffers,
verbose) and then rollback?


*Michael Lewis  |  Database Engineer*
*Entrata*

Re: Max connections reached without max connections reached

[Michael Lewis]

On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent  wrote:

> Also be on the look-out for begin trans; begin trans; etc  I read Kumar's
> report as nested transactions.  If your gang is doing a transaction per
> row, they need a kick in the rear.  Anyone not closing those needs a pink
> slip.
>

I have seen the second attempt at calling begin seem to be ignored with
"WARNING:  there is already a transaction in progress" similar to calling
rollback without a transaction open. Are you stating or suggesting that
calling begin a second time actually is more similar to
creating a savepoint? That would be rather unexpected from the messaging,
at least for me.

Re: Max connections reached without max connections reached

[Michael Lewis]

On Wed, Dec 1, 2021 at 8:54 PM Rob Sargent  wrote:

> On 12/1/21 7:08 PM, Michael Lewis wrote:
>
> On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent  wrote:
>
>> Also be on the look-out for begin trans; begin trans; etc  I read
>> Kumar's report as nested transactions.  If your gang is doing a transaction
>> per row, they need a kick in the rear.  Anyone not closing those needs a
>> pink slip.
>>
>
> I have seen the second attempt at calling begin seem to be ignored with
> "WARNING:  there is already a transaction in progress" similar to calling
> rollback without a transaction open. Are you stating or suggesting that
> calling begin a second time actually is more similar to
> creating a savepoint? That would be rather unexpected from the messaging,
> at least for me.
>
> No.  Hard to know what the devs are up to here (what stack they're using,
> etc), but I'm suggesting one transaction is getting started and not closed.
> Warnings have been know to be ignored.  I admit I am not familiar with
> Kumar's term of "sub-transaction" and how those might be created but it
> sounds to me like a transaction is begun early in the processing and more
> work happens unaware of the transaction.  And possible not committed.
>

Okay cool. Subtransactions are interesting. They are used when a savepoint
is issued, and with exception blocks in PL/pgSQL functions. They allow the
system to keep track of which "sub" process made changes to rows. So just
like the main transaction gets a transaction ID and rows get marked with
xmin or xmax as needed for MVCC, the subtransactions get their own
transaction ID and the objects they modify get updated to set xmin and xmax
with their transaction ID. There are some interesting performance
bottlenecks when many subtransactions get involved, but I am still learning
about that.

Disclaimer- I am no kind of PG contributor and very much still learning,
the above explanation is my best understanding but I welcome any
corrections or clarifications.

Re: Max connections reached without max connections reached

[Michael Lewis]

On Wed, Dec 1, 2021 at 9:06 PM Dilip Kumar  wrote:

> IMHO, it is good to LOG such information if we are not already logging
> this anywhere.
>

+1

I expect my 'vote' counts for naught, but I fully expect seeing these show
up in the logs would have helped me much more quickly have insight into
what was going on during times of very high concurrency and extreme
slowness with many processes showing as waiting on LwLocks.

Re: INSERT ... ON CONFLICT doesn't work

[Michael Lewis]

Perhaps I missed something, but why all this effort to reference the column
and not just reference IX_Lockers_Uuid for the on conflict clause?

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

[Michael Lewis]

What is your default_statistics_target and how accurate is that estimate of
5668 rows? What is random_page_cost set to by the way?

More importantly, what is the better plan that you'd like the planner to
use with your existing indexes? It would seem logical to me to scan for the
matching shipment_import_id if the estimate is saying 5868 out of 29
million should match and then sort and only get the smallest ID. Doing an
index scan on ID and looking up in the table to see if shipment_import_id
matches when the planner expects that to be about a .0001 chance... I can't
imagine that plan performing well at all.

Certainly a composite index would be very helpful here. Using explain
analyze and sharing the output would give more info to go on.

>

Re: Postgresql + containerization possible use case

[Michael Lewis]

On Fri, Dec 10, 2021, 2:24 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> Hi
> we are running some 140 remote servers (in the 7 seas via satellite
> connections)


How are they used? What is in Postgres? Should that all have the exact same
read only data at all times?

>

Re: Postgresql + containerization possible use case

[Michael Lewis]

Interesting. I'm not sure that moving an image for a VM with Postgres is
compatible with the goal of minimizing time/data but if upgrades are yearly
or something, perhaps that is reasonable. It has been 9+ years since that
post as well. But the full data will need to be included in the image if
the goal is not requiring any (significant) tech support at each location,
right? Unless the data that needs to be stored at each site is small, that
seems a bit unlikely to be a feasible option.

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[Michael Lewis]

On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule 
wrote:

> I wrote about it. Did you read this article?
>
> https://okbob.blogspot.com/2018/02/schema-variables.html
>
> The goals of this project:
>
>- fast non transactional non persistent (session based) storage,
>
> Would there be statistics that are considered in query planning, or would
that be impossible or just out of scope initially?

Default values in functions

[Michael Lewis]

I am on PG 13.4 and found some surprising behavior with default values. Can
anyone give insight why the last two cases (especially the last one) do not
give the result I expected? If I uncomment the line to set pArrayToCheck
with coalesce, then it gives the expected results though.

If I can somehow pass "use default for this parameter" to functions like I
can for insert statements, then that would be great to know. Generally, the
use case I am looking at is having a function with a few required
parameters leading and then many optional and I'd like to ensure the
optional ones get the default set if a value is used that "is not distinct
from null" basically. Hopefully the example is clear.


CREATE OR REPLACE FUNCTION public.check_if_input_is_empty_array(
pArrayToCheck integer[] DEFAULT ARRAY[]::integer[], inout pTimeToDisplay
timestamptz DEFAULT CURRENT_DATE::timestamptz, out lReturnValue boolean )
LANGUAGE plpgsql
AS $function$
BEGIN
--pArrayToCheck = COALESCE( pArrayToCheck::integer[],
ARRAY[]::integer[] );

SELECT icount ( pArrayToCheck ) = 0 INTO lReturnValue;
SELECT pTimeToDisplay AT TIME ZONE 'UTC' INTO pTimeToDisplay;

END;
$function$;

select ( check_if_input_is_empty_array() ).*, true as expected_result
UNION ALL
select ( check_if_input_is_empty_array( pArrayToCheck, pTimeToDisplay )
).*, expected_result
from(
values
( ARRAY[]::int[], CURRENT_DATE + interval '1 hour', true ),
( ARRAY[1]::int[], CURRENT_DATE + interval '2 hour', false ),
( null::int[] , CURRENT_DATE + interval '3 hour', true ),
( null , CURRENT_DATE + interval '4 hour', true )
)AS sub ( pArrayToCheck, pTimeToDisplay, expected_result );


*Michael Lewis  |  Database Engineer*
*Entrata*

Re: Default values in functions

[Michael Lewis]

On Wed, Dec 29, 2021, 4:34 PM David G. Johnston 
wrote:

> That isn’t how it works.  Absence is what is important.  Null is not
> absence.  As you showed, if you want nulls to be converted to defaults you
> can use coalesce.
>

Thanks sir. It seems unfortunate that there is not a way to indicate
absence of the third parameter if I need to set a value for the fourth
parameter.

Re: Default values in functions

[Michael Lewis]

On Wed, Dec 29, 2021, 4:31 PM Tom Lane  wrote:

> You didn't say what icount() is, but if it's the one from
> contrib/intarray, it's STRICT meaning it'll return NULL,
> not zero, for a null array input.


Thanks for that. Very good to know.

(Or IOW, null::int[] is not at all the same thing as array[]::int[].)
>

Understood. My hope is to pass a parameter that gets overridden by the
default so that I can pass other parameters that come after.


> SELECT pTimeToDisplay AT TIME ZONE'UTC' INTO pTimeToDisplay;
>
> This is very unlikely to produce anything sane.


Sorry for that confusion. It was intended as an example of non default
later in the list after a default.

Re: Default values in functions

[Michael Lewis]

On Wed, Dec 29, 2021, 5:31 PM Tom Lane  wrote:

> The way to do that is to use named parameters and the associated
> call syntax, ie something like
>
> select myfunc(param1 => 42, param3 => 99);
>

Thanks very much. I have not seen that before.

>

Re: Query much slower from php than psql or dbeaver

[Michael Lewis]

When dealing with foreign tables, I believe planning is not the same
because of access to statistics (maybe has improved since 9.6 though). I
just wonder... Would it be a viable option to create a materialized view
using the FDW but then use the PHP script against the local tables only?
Materialized views are not maintained automatically, but you have local
statistics and can create indexes. Just a thought in case the data is not
changing constantly and this might fit the need.

Also, it seems like perhaps the foreign queries might be more targeted if
some data was encouraged to be pre-computed. What would be the expected row
count from just table1?

Note- your explain plan doesn't mention "fichero_origen" field name. Is
that just a copy/paste error?


with cte_interesting_t1_rows_precomputed AS materialized(
select
t1.*,
substring(t1.bbb from 1 for 3) in () AND t1.file =
'file_name.csv' AS needs_t2,
substring(t1.bbb from 1 for 3) in () AS needs_t3
FROM
table1 t1
where t1.c = 'ACTIVE'
and t1.fichero_origen = 'file_name.csv'
)

 select t1. as maindb_, t1.bbb as maindb_bbb, t1.c as
maindb_c, t1.timestamp_create as maindb_create,
t1.timestamp_closed as maindb_close, t1.d as maindb_d,
null::text as db1_sth,
t2. as db1_, t2. as db1_, null::text as
db2_sth,
t3. as db2_, t3. as db2_
from cte_interesting_t1_rows_precomputed AS t1
left join database1_fdw.table2 AS t2 on t1. = t2.btatpd_ and
t2.btatpd_fecha = '2022011912' AND needs_t2
left join database2_fdw.table2 AS t3 on t1. = t3.btatpd_ and
t3.btatpd_fecha = '2022011912' AND needs_t3
where
(t2. is null and t3. is null)
or
(t2. is not null and t1.d <> t2.)
or
(t3. is not null and t1.d <> t3.)
order by t1.bbb nulls last;

Re: tstzrange on large table gives poor estimate of expected rows

[Michael Lewis]

If interval_end_date is always 1 day ahead, why store it at all?

Dependencies on a custom stats object wouldn't do anything I don't think
because they are offset. They are 100% correlated, but not in a way that
any of the existing stat types capture as far as I can figure.

Re: Undetected Deadlock

[Michael Lewis]

I must be missing something. You mentioned dropping a partition, so is
there an actual need for the delete? Could you detach concurrently and then
drop the table or delete rows if needed?

https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION

Re: Undetected Deadlock

[Michael Lewis]

There may be a bug so perhaps still pursue reproducing the issue, but I
would expect drop concurrently to resolve your issue with the two processes
conflicting. Also, perhaps trying"insert, on conflict do update" could be
more efficient than the copy but obviously there are too many unknowns and
variables for either to be a definite winner.

Re: pg_try_advisory_lock is waiting?

[Michael Lewis]

pg_try_advisory_lock and other advisory lock functions take a lock on a
key. The keys you have tried to lock are 0 and 1. Why?

What problem are you trying to solve by changing from "for update" lock to
advisory lock anyway?

Re: pg_try_advisory_lock is waiting?

[Michael Lewis]

On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala 
wrote:

> pg_try_advisory_lock returned TRUE even without "FOR UPDATE" clause in the
> subquery. Shouldn't it return false because it can't lock the row until the
> uncommitted update finishes?
>
>  
>
> The rows being updated or not is unrelated to advisory locks. Your example
shows you locking on key 0 and then on key 1. That should be allowed. If
you tried key 0 twice, then that is when you would get back "false" from
the function call.

You could establish a pattern of using the table OID as the first lock key
and the primary key value (if a single column) as the second advisory lock
key with the two parameter version of the function. But it is totally up to
your code to honor that advisory lock, or not.

Again, why use advisory locks and not select for update? Perhaps just
because you don't want to deal with the failed transaction? What should
happen when some other process cannot get a lock on that row? Do you want
to wait and retry? Have you looked into the "skip locked" option? If you
use "returning id" with that, you'd be able to check if you got the lock or
not.

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

[Michael Lewis]

What does the row estimate look like on the scan of data table with that
statistic in place? Anytime the stats give a mis-estimate this far off, I
wouldn't expect that plans would be optimal except by luck.

Index Scan using data_pkey on data t (cost=0.57..21427806.53 *rows=58785023*
width=131) (actual time=0.024..0.482 *rows=854 *loops=1)
Index Cond: (id > 205284974)

Re: pg_cron for vacuum - dynamic table set

[Michael Lewis]

Can't you use a do script to construct and execute the statement?

>

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

[Michael Lewis]

I would guess that autovacuum is analyzing the table and causing the stats
to change which is resulting in different estimates that result in
different plans. Unless you can get the estimate much more accurate, you
won't get far with expecting a stable plan that performs well.

How is data_class_pkey? If you run a query like this, how far off are the
estimates?


explain analyze
select d.time as time,d.id as id, a.query_symbol as query_symbol

from
data as d
join data_class as dc ON dc.data_id = d.id
join class as a ON dc.class_id = a.id
where
d.id > 205284974
order by d.id
limit 1000;

If you run 'analyze data( id );' and then run the query again, do you get a
better estimate? Have you tried adjusting default_stats_target? Are you
running the default value for random_page_cost with SSDs?

I'm seeing Index Only Scan nodes, but a high number of fetches so it seems
like you would benefit from vacuum to update pg_class.relallvisible value.

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

[Michael Lewis]

I think that theoretically if the planner sees a condition like

a.column1 > constant_value, and it also has a condition like a.column1 =
b.column2 then it could autogenerate the b.column2 > constant_value
condition. And of course > could be <, <=, >= and <>

But I could be wrong, particularly with respect to nulls and how = is
mostly the same as 'is not distinct from' except for 'select null = null'
being null and 'select null is not distinct from null' being true. Perhaps
if either/both columns have a not null constraint, then this could still be
done. Whether or not it should be a separate question.

Re: Rows From but with Subqueries (or a cleaner non-array-using alternative)?

[Michael Lewis]

In pseudo code, group_index is defined as:
case when LAG(v) OVER (ORDER BY i) = v then lag(i) ELSE i END, right?

If you have that in the first cte instead of the start/end business, then
you can just select vals, group number, and row_num over that new grouping,
right?

Something like this?


WITH vals (i,v) AS (VALUES
(0,1),(1,0),(2,0),(3,1),(4,0),(5,0),(6,1),(7,1),(8,0),(9,1)),
grouped_vals AS (SELECT *,
case when LAG(v) OVER (ORDER BY i) = v then lag(i) OVER (ORDER BY i) ELSE i
END AS group_index
FROM vals
)
select *, row_number() OVER (PARTITION BY group_index ORDER BY i)
from grouped_vals
where v = 0;

Re: PostgreSQL's Max Identifier Length in Amazon RDS

[Michael Lewis]

I believe that would not be possible. One quick read-
https://til.hashrocket.com/posts/8f87c65a0a-postgresqls-max-identifier-length-is-63-bytes

With it being RDS, changing source and recompiling is not an option.

Re: PostgreSQL's Max Identifier Length in Amazon RDS

[Michael Lewis]

Also-
https://www.postgresql.org/docs/10/runtime-config-preset.html

"The following “parameters” are read-only, and are determined when
PostgreSQL is compiled or when it is installed."

>

Serializable read only deferrable- implications

[Michael Lewis]

https://www.postgresql.org/docs/14/sql-set-transaction.html

"The DEFERRABLE transaction property has no effect unless the transaction
is also SERIALIZABLE and READ ONLY. When all three of these properties are
selected for a transaction, the transaction may block when first acquiring
its snapshot, after which it is able to run without the normal overhead of
a SERIALIZABLE transaction and without any risk of contributing to or being
canceled by a serialization failure. This mode is well suited for
long-running reports or backups."


Could anyone expound on the above? What are the 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  |  Database Engineer*
*Entrata*

Re: Serializable read only deferrable- implications

[Michael Lewis]

>
> 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

[Michael Lewis]

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/transaction-iso.html#XACT-SERIALIZABLE
>
> and
>
>
> https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY



Thanks to you both. If other concurrent sessions are using default
isolation level of Read committed, would putting long running reports
(read-only) into that read-only serializable deferrable mode be impactful
at all?

The documentation says that a transaction ID is only assigned to a
connection once a write is done, but is the assignment or not of a txn id
actually impactful on anything? I ask partly because it doesn't seem
possible to reset that once assigned, through discard all; or something
else like that which might be used by a connection pooler such as pg
bouncer. is there any way to check if a session has "done writes/updates up
to this point"? It seems pg_my_temp_schema() also returns the same value
even after 'discard temp' or 'discard all' is executed. That was surprising
to me, but would it be considered an issue by anyone?

Re: foreign key on delete cascade order?

[Michael Lewis]

>
> 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 SHARE UPDATE
EXCLUSIVE (pretty sure that would be the proper level), then retry if it
fails because a delete is already going on?

Also, are you confident that before you call 'begin' to do the update, you
are not already in a transaction which might have some lock on row(s) in
mach, or one of the other tables involved?

Re: delete query using CTE

[Michael Lewis]

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.

[Michael Lewis]

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

[Michael Lewis]

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

[Michael Lewis]

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 SERIALIZABLE READ ONLY DEFERRABLE in terms of impact on logical or
physical replication, autovacuum, etc even if the much heavier OLTP
traffic is still running as the default read committed mode?

If the OLAP queries are moved to a physical read replica, I understand from
this post (
https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
) that there are chances that a query will be killed on the replica even
with hot_standby_feedback is turned on. With them running on the same
server, is the main concern (other than load) that vacuum type cleanup is
delayed?

Maybe to sum up- If a long running report type query is run in default
"read committed" mode and uses no temp tables / does no writes, would there
be a benefit or change in behavior when using SERIALIZABLE READ ONLY
DEFERRABLE mode?

LwLocks contention

[Michael Lewis]

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
processes, there will suddenly be ~300 and many show active with LwLock and
they take much longer than usual. Any suggested options to monitor for such
issues or logging to setup so the next issue can be debugged properly?

It has seemed to me that this occurs when there are more than the usual
number of a particular process type and also something that is a bit heavy
in usage of memory/disk. It has happened on various tenant instances and
different application processes as well.

Would/how might the use of huge pages (or transparent huge pages, or OFF)
play into this scenario?

Re: LwLocks contention

[Michael Lewis]

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, huge number of indexes, etc etc.

There are lots of things to fix, but I just do not yet have a good sense of
the most important thing to address right now to reduce the odds of this
type of traffic jam occurring again. I very much appreciate you sharing
your experience. If I could reliably reproduce the issue or knew what data
points to start collecting going forward, that would at least give me
something to go on, but it feels like I am just waiting for it to happen
again and hope that some bit of information makes itself known that time.

Perhaps I should have posted this to the performance list instead of
general.

Re: Is this a reasonable use for advisory locks?

[Michael Lewis]

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?
That way, no need to hold that db connection while doing external work via
api.

>

Re: perf tuning for 28 cores and 252GB RAM

[Michael Lewis]

>
> 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 want to use pg_prewarm after each restart to get the data into
> cache as fast as you can, rather than let it get loaded in naturally as you
> run queries;  Also, you would probably want to set random_page_cost and
> seq_page_cost quite low, like maybe 0.1 and 0.05.
>

In all deference to your status as a contributor, what are these
recommendations based on/would you share the rationale? I'd just like to
better understand. I have never heard a recommendation to set random & seq
page cost below 1 before for instance.

If the entire database were say 1 or 1.5 TBs and ram was on the order of 96
or 128 GBs, but some of the data is (almost) never accessed, would the
recommendation still be the same to rely more on the OS caching? Do you
target a particular cache hit rate as reported by Postgres stats?

>

Re: PostgreSQL 11 Auto vacuum

[Michael Lewis]

>
> 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 allowed before autovacuum
stops (autovacuum_vacuum_cost_limit) or perhaps increase the number of
workers? What is the update/deletes workload balance? That is, would it
make sense to decrease the fillfactor on these tables such that you get
more HOT (heap only tuple) updates and indexes are less bloated to get
better performance that way? How often are you manually vacuuming?

Re: PostgreSQL upgrade from 9.4.2 to 9.6.12

[Michael Lewis]

This may also be of interest to you, but checking official docs as Adrian
recommended is best.
https://why-upgrade.depesz.com/show?from=9.4.2&to=9.6.12&keywords=

Re: Partitioning an existing table - pg10.6

[Michael Lewis]

I have not personally used this, but the write-up seems solid to minimize
downtime to help you to shift data gradually. Be sure you understand the
limitations of partitioning, particularly when you are still on 10x not yet
on v11 where updates will shift a row to a new partition if the partition
key is updated.

https://www.depesz.com/2019/03/19/migrating-simple-table-to-partitioned-how/

Re: Too slow to create new schema and their tables, functions, triggers.

[Michael Lewis]

On Tue, Jul 23, 2019 at 1:36 PM Adrian Klaver 
wrote:

> On 7/23/19 12:20 PM, PegoraroF10 wrote:
> > We have in a single database 190 identical schemas. Now, when we create
> a new
> > one, with exactly same structure as the previous ones, it takes 20 or 30
> > minutes to finish. Usual time to finish that script was 30 seconds.


 Can you create 200 schemas using your script without the data load step
and reproduce the issue? With 19,000 tables on that database, how
aggressive is autovacuum?

Re: How to check if a field exists in NEW in trigger

[Michael Lewis]

As a note to the original poster, you might want to check out-

https://www.postgresql.org/docs/current/citext.html

Re: slow queries on system tables

[Michael Lewis]

Are you autovacuuming and analyzing aggressively enough? Is there bloat or
other concern for these system tables? I expect it may be a concern based
on what I remember about your environment having thousands of tables and
perhaps autovacuum not keeping up (re: Too slow to create new schema and
their tables, functions, triggers).

Can you check results for last (auto)vacuum and analyze?
select * from pg_stat_all_tables where relname IN( 'pg_class',
'pg_attribute', 'pg_index' );

Re: Complex filters -> Bad row estimates -> bad query plan

[Michael Lewis]

If those conditions that are throwing off the stats are expected to be
minimally impactful/filtering few rows, then you can use the one
tried-and-true optimizer hint (aside from materialized CTEs, stylized
indexes, etc) --- OFFSET 0 at the end of a sub-query.

SELECT * FROM ( [your existing query without the sub-selects that are
complicated and produce bad estimates] OFFSET 0 ) WHERE [your other
conditions that don't produce good estimates]

If there is correlation between field1 and field2, you might also look at
CREATE STATISTICS assuming you are on PG 10 or 11.

Before I do any of that, I would try LEFT JOIN for Table3 and Table4 then
use the where conditon  "AND 2 = COALESCE( Table3.Status, Table4.Status"
and see if the optimizer likes that option better.

Re: Complex filters -> Bad row estimates -> bad query plan

[Michael Lewis]

-- I'm thinking the OFFSET 0 create an optimization barrier that prevents
the planner from collapsing that sub-query into the top query, and enforces
ordering in the query?

That's my understanding. I think it is an optimizer hint by another name. I
used to put things in a CTE (which is always materialized until v12, which
will change it to inlined unless keyword MATERIALIZED is included) or I
would create a temp table if the dataset is expected to contain many rows
such that I can do ANALYZE pg_temp.table_table; so the optimizer has stats
to make good decisions.

Note- Replying to messages with a full quote of the conversation below your
comment (aka top-posting) is discouraged on these mailing lists. Please
quote the portion you are responding to and that's it.

Support for using alias in having clause

[Michael Lewis]

Why can I use an alias for group by and order by and not for having? I am
just wondering if there is some barrier to it being implemented, or if it
just hasn't been.

select
table_schema || '.' || table_name AS schema_qualified_table_name,
count( column_name ) as column_count
from
information_schema.columns
group by
schema_qualified_table_name
having
count( column_name ) > 50 /* this works, but trying column_count > 50 does
not */
order by
column_count desc;

Re: How to use brin_summarize_range

[Michael Lewis]

What is default_statistics_target set to? Or is there custom statistics
value set for this table/column? Perhaps the planner is making sub-optimal
choices because it only has a vague idea about the data and the histogram
is not very informative. Planning time will increase when statistics target
is increased, but perhaps 250 instead of default 100 would give *enough*
more information to make better choices. Or perhaps your target is already
max 1 and then I have no idea why btree would be chosen. Except, if
correlation is too low for the optimizer to consider BRIN to be best
perhaps. What does pg_stats say about the column in question?

>

Re: slow queries on system tables

[Michael Lewis]

Might the issue be with pg_index or pg_attribute rather than pg_class? Is
the query still slow? Do you have the output of explain analyze for use on
https://explain.depesz.com/ or the output of EXPLAIN (ANALYZE, COSTS,
VERBOSE, BUFFERS, FORMAT JSON) for use on
http://tatiyants.com/pev/#/plans/new website?

Re: slow queries on system tables

[Michael Lewis]

Can you manually execute vacuum analyze on all three involved tables and
again share the plan for the same query? If it is significantly improved,
it would seem like following the recommendation to tune autovacuum (and
analyze) to be more frequent would be prudent.

You haven't seemed to change from default vacuum/analyze settings despite
the default parameters being suggested only for bare minimum hardware and
very light use databases.

Re: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?)

[Michael Lewis]

On Tue, Aug 27, 2019 at 9:45 PM Laurenz Albe 
wrote:

> Holtgrewe, Manuel wrote:
> > Switching off fsync leads to a drastic time improvement but still
> > higher wall-clock time for four threads.
>
> Don't do that unless you are ready to start from scratch with a new
> "initdb" in the case of a crash.
>
> You can do almost as good by setting "synchronous_commit = off",
> and that is crash-safe.


It seems like it depends on your definition of crash-safe. Data loss can
occur but not data corruption, right? Do you know any ballpark for how much
difference in performance it makes to turn off synchronous_commit or what
type of hardware or usage it would make the biggest (or least) difference?

Re: Query using 'LIKE' returns empty set

[Michael Lewis]

You need the wildcard character at front and back.

select * from fish_counts where stream_tribs ilike '*%*Nehalem*%*';

Re: Bad Estimate for multi tenant database queries

[Michael Lewis]

>
> CREATE STATISTICS MT_ReservationDepartureUtc (ndistinct) on "AccountCode",
> "DepartureUtc" from "Reservation"."Reservation";
> CREATE STATISTICS MT_ReservationArrivalUtc (ndistinct) on "AccountCode",
> "ArrivalUtc" from "Reservation"."Reservation";
> CREATE STATISTICS MT_ReservationNoShowFeeId (ndistinct) on "AccountCode",
> "NoShowFeeId" from "Reservation"."Reservation";
> CREATE STATISTICS MT_ReservationTimeSliceDefinitionId (ndistinct) on
> "AccountCode", "TimeSliceDefinitionId" from "Reservation"."Reservation";
>
> but that didn't help either
>

Did you try with 'dependencies' for the statistics_kind or only ndistinct?
What was default_statistics_target set to at the time you created the
extended statistics? I am not sure if that value is used, but I would
assume so.

Re: literal vs dynamic partition constraint in plan execution

[Michael Lewis]

I think I would expect this behavior with how you have defined the
constraints as the function results rather than just being strict
comparisons to the timestamp field.

Instead of this-
Check constraints:
> "y2018_mis_ora_check" CHECK (date_part('year'::text, mis_ora) =
> 2018::double precision)

I would expect this-
Check constraints:
> "y2018_mis_ora_check" CHECK mis_ora) >= make_timestamp(2018, 1,
> 1, 0, 0, 0::double precision) AND < make_timestamp(2019, 1,
> 1, 0, 0, 0::double precision)

Re: pg full text search very slow for Chinese characters

[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 column. Pg could not finish creating a GIN index for a long time and I
had to cancel the execution.I then tried to create a partial full text
index for 500 rows and it took postgres 2 to 3 minutes to create the index.


Did you customize any config? maintenance_work_mem specifically would be
relevant to the time to create an index and default value is only 64MB.
Especially if you are running a spinning hard drive and not ssd, then this
could be problematic.

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

[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 generated by a sequence when you
have a natural key of either cust_no or name? Why not just declare the
customer number to be the PK? Where does customer number come from anyway?
Using smallint seems potentially short-sighted on potential future growth,
but changing the type later should be minimal work as long as you don't
have this customer_number denormalized many places, or use it as the FKey
after dropping customer_key surrogate key.

Re: problems importing from csv

[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 a temp table), remove $ characters,
cast value and insert into numeric field

By the way, there may be no benefit to specifying a max value of 99,999.99
if you can foresee a potential need for bigger values. For values that fit,
numeric(1000,2) will store numbers in the same bytes as a numeric(5,2)
field will. It just won't throw an error if large values are used in the
future.

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

[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