Re: Automatic aggressive vacuum on almost frozen table takes too long

[Peter Geoghegan]

On Thu, Feb 16, 2023 at 5:40 PM Mikhail Balayan  wrote:
>> >> Do you have any non-btree indexes on the table? Can you show us the 
>> >> details of the
>> >> table, including all of its indexes? In other words, can you show "\d 
>> >> applications" output from psql?
>
> Only btree indexes. Please find the full table schema below:

It's possible that VACUUM had to wait a long time for a cleanup lock
on one individual heap page here, which could have added a long delay.
But...that doesn't seem particularly likely.

Can you run amcheck's bt_index_check() routine against some of the
indexes you've shown? There is perhaps some chance that index
corruption exists and causes VACUUM to take a very long time to delete
index pages. This is pretty much a wild guess, though.

-- 
Peter Geoghegan

Re: Query plan for "id IS NULL" on PK

[Ben Chrobot]

Thank you all for your responses!

I will continue to put pressure on the vendor (Stitch Data, if anyone knows
folks there) to address the issue on their end with the query being issued.

Best,
Ben Chrobot


On Tue, Feb 14, 2023 at 11:11 PM Tom Lane  wrote:

> David Rowley  writes:
> > On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer  wrote:
> >> OTOH it could also be argued that the optimizer should be able to
> >> perform the same simplifications as I did above and produce the same
> >> code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
> >> as for WHERE (("id" > ?)) AND (("id" <= ?)).
>
> > You're right, and it has been brought up quite a few times in the
> > past.  To make it work, it's a fairly trivial change. We'd just need
> > to record all the attnotnull columns during something like
> > get_relation_info() then when adding baserestrictinfos to the base
> > relations, we could look to see if the qual is a NullTest and skip
> > that if we deem the qual as constantly true.
>
> There's an order-of-operations issue that makes this more painful
> than you might think at first.  In the above example, the NullTest
> node *isn't* going to be a top-level restrictinfo: it's buried inside
> an OR.  Really, the only reasonable place to suppress such a NullTest
> is during eval_const_expressions, which already has the logic that would
> get rid of the now-unnecessary OR above it.  And that's problematic
> because it's done way ahead of where we know any relation-specific
> information.  (Since eval_const_expressions happens ahead of join
> removal, for $good_reasons, moving the plancat.c fetching to someplace
> earlier than that wouldn't be cost-free either.)
>
> > The problem with that is that doing that has an above zero cost and
> > since it likely only applies to nearly zero real-world cases, it just
> > does not seem like useful cycles to add to the planner.
>
> Yeah, this.  In the end there is a low threshold on expensive stuff
> that we're willing to do to clean up after brain-dead ORMs, because
> the costs of that will also be paid by not-so-brain-dead applications.
> In the example at hand, it's hard to argue that the query generator
> sending this query shouldn't know better, since as Peter points out
> the IS NULL check is redundant on its face, primary key or not.
>
> regards, tom lane
>
>
>

Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

[Marc Millas]

If I may..
this answer looks more "philosophical" than "practical".
On Oracle (maybe 10, I don't remember well) was introduced the possibility
to explicitly store an execution plan, so that a given query use THAT plan
ie. dont go thru planner job.
OK if someone do stupid things, one may get stupid results...it was an
"expert only" functionality  :-)
Still, in some cases, it was very useful to manage the rare cases where the
planner cannot, for whatever reason do a good job.

OK its not the way postgres do behave. Still, in some cases...



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Feb 16, 2023 at 5:08 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Feb 16, 2023 at 8:48 AM cen  wrote:
>
>>
>> - does the planner take previous runs of the same query and it's
>> execution time into account? If not, why?
>>
>
> No, because that isn't how it works.  And while I'm no planner expert I'm
> not imagining any particularly compelling argument for why it would even
> make sense to try.  The book-keeping would be expensive and dealing with
> supposedly an ever-changing dataset would in many cases make any such
> comparisons be meaningless.
>
>
>> - assuming the query to be immutable, would it be possible for the
>> planner to microbenchmark a few different plans instead of trying to
>> estimate the cost?
>> As in, actually executing the query with different plans and caching the
>> best one.
>>
>
> No, the planner may not cause execution.  While I could imagine extending
> EXPLAIN to somehow retrieve and maybe even try alternative plans that have
> been fully constructed today I'm not holding my breath.
>
> There is little reason for the project to give any real weight to
> "assuming the query to be immutable".  We do want to fix the planner to
> behave better if it is mis-behaving, otherwise you do have access to cost
> parameters, and potentially other planner toggles if you've truly run into
> an intractable problem.
>
> David J.
>
>

Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

[cen]

On 16/02/2023 17:07, David G. Johnston wrote:


No, the planner may not cause execution.  While I could imagine 
extending EXPLAIN to somehow retrieve and maybe even try alternative 
plans that have been fully constructed today I'm not holding my breath.


There is little reason for the project to give any real weight to 
"assuming the query to be immutable".  We do want to fix the planner 
to behave better if it is mis-behaving, otherwise you do have access 
to cost parameters, and potentially other planner toggles if you've 
truly run into an intractable problem.


David J.

Fair on both points. I didn't know planner toggles existed, I'll play 
with that.

Sv: PostgreSQL configuration in a VM

[Andreas Joseph Krogh]

På fredag 17. februar 2023 kl. 09:13:10, skrev Sebastien Flaesch <
sebastien.flae...@4js.com >:
Hi!


I was wondering if the is any specific configuration setting that should be 
used with PostgreSQL, when running in a VM...


Is there anything obvious that must be set, to get best performances with such 
a config?


Sorry for this general question...
In my experience the most important parameter when running in a VM is 
random_page_cost, and for that to be set to a sane value you need to know the 
characteristics of the disk available to your VM. In other words, disk IO is 
what you should be worried about as VMs are pretty good at scaling CPU-wise.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

PostgreSQL configuration in a VM

[Sebastien Flaesch]

Hi!

I was wondering if the is any specific configuration setting that should be 
used with PostgreSQL, when running in a VM...

Is there anything obvious that must be set, to get best performances with such 
a config?

Sorry for this general question...

Seb