Re: Row estimates for empty tables

[Tom Lane]

David Rowley  writes:
> On Fri, 24 Jul 2020 at 16:01, Christophe Pettus  wrote:
>> I realize I've never quite known this; where does the planner get the row 
>> estimates for an empty table?  Example:

> We just assume there are 10 pages if the relation has not yet been
> vacuumed or analyzed. The row estimates you see are the number of
> times 1 tuple is likely to fit onto a single page multiplied by the
> assumed 10 pages.  If you had made your table wider then the planner
> would have assumed fewer rows

Yeah.  Also note that since we have no ANALYZE stats in this scenario,
the row width estimate is going to be backed into via some guesses
based on column data types.  (It's fine for fixed-width types, much
less fine for var-width.)

There's certainly not a lot besides tradition to justify the exact
numbers used in this case.  However, we do have a good deal of
practical experience to justify the principle of "never assume a
table is empty, or even contains just one row, unless you're really
sure of that".  Otherwise you tend to end up with nestloop joins that
will perform horrifically if you were wrong.  The other join types
are notably less brittle.

regards, tom lane

Re: Row estimates for empty tables

[Christophe Pettus]

> On Jul 24, 2020, at 06:48, Tom Lane  wrote:
> 
> There's certainly not a lot besides tradition to justify the exact
> numbers used in this case. 

Since we already special-case parent tables for partition sets, would a storage 
parameter that lets you either tell the planner "no, really, zero is reasonable 
here" or sets a minimum number of rows to plan for be reasonable?  I happened 
to get bit by this tracking down an issue where several tables in a large query 
had zero rows, and the planner's assumption of a few pages worth caused some 
sub-optimal plans.  The performance hit wasn't huge, but they were being joined 
to some *very* large tables, and the differences added up.
--
-- Christophe Pettus
   x...@thebuild.com

Re: CASCADE/fkey order

[Michel Pelletier]

You can benchmark your scenario with and without constraint using a tool
like nancy:

https://gitlab.com/postgres-ai/nancy

it lets you A/B test different configurations with your own scenarios or
using pgbench synthetic workloads.

-Michel

On Wed, Jul 22, 2020 at 9:27 AM Samuel Nelson 
wrote:

> I checked, and changing the `bazinga_foo_bar` constraint to:
>
> alter table bazinga add constraint bazinga_foo_bar foreign key (foo_id,
> bar_id) references foo_bar (foo_id, bar_id) deferrable initially deferred;
>
> seems to fix it to work as we were expecting.  Is that particularly
> costly?  Should I only set the constraint to be deferred when we really
> need it?  Would it be more efficient to perform the deletes explicitly
> within a transaction rather than relying on the cascades and deferring that
> one constraint?
>
> Our resident ex-Oracle DBA said that deferred constraints used to be
> heavily recommended against, but he also admitted that he hasn't kept up
> with that in the past 10 years.
>
> -Sam
>
> https://github.com/nelsam
>
> "As an adolescent I aspired to lasting fame, I craved factual certainty,
> and
> I thirsted for a meaningful vision of human life -- so I became a
> scientist.
> This is like becoming an archbishop so you can meet girls."
> -- Matt Cartmill
>
>
> On Wed, Jul 22, 2020 at 10:31 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Wed, Jul 22, 2020 at 8:24 AM Samuel Nelson 
>> wrote:
>>
>>> Is there a way to force the delete to cascade to tables in a specific
>>> order?
>>>
>>
>> No really, but you can defer constraint checking.
>>
>> https://www.postgresql.org/docs/12/sql-set-constraints.html
>>
>> David J.
>>
>>
>

Re: Row estimates for empty tables

[Pavel Stehule]

pá 24. 7. 2020 v 16:38 odesílatel Christophe Pettus 
napsal:

>
>
> > On Jul 24, 2020, at 06:48, Tom Lane  wrote:
> >
> > There's certainly not a lot besides tradition to justify the exact
> > numbers used in this case.
>
> Since we already special-case parent tables for partition sets, would a
> storage parameter that lets you either tell the planner "no, really, zero
> is reasonable here" or sets a minimum number of rows to plan for be
> reasonable?  I happened to get bit by this tracking down an issue where
> several tables in a large query had zero rows, and the planner's assumption
> of a few pages worth caused some sub-optimal plans.  The performance hit
> wasn't huge, but they were being joined to some *very* large tables, and
> the differences added up.
>

I did this patch ten years ago.  GoodData application
https://www.gooddata.com/  uses Postgres lot, and this application stores
some results in tables (as guard against repeated calculations). Lot of
these tables have zero or one row.

Although we ran an ANALYZE over all tables - the queries on empty tables
had very bad plans, and I had to fix it by this patch. Another company uses
a fake one row in table - so there is no possibility to have a really empty
table.

It is an issue for special, not typical applications (this situation is
typical for some OLAP patterns)  - it is not too often - but some clean
solution (instead hacking postgres) can be nice.

Regards

Pavel

> --
> -- Christophe Pettus
>x...@thebuild.com
>
>
>
>

when is RLS policy applied

[Ted Toth]

I'm trying to understand when RLS select policy is applied so I created the
follow to test but I don't understand why the query filter order is
different for the 2 queries can anyone explain?

CREATE USER bob NOSUPERUSER;
CREATE TABLE t_service (service_type text, service text);
INSERT INTO t_service VALUES
('open_source', 'PostgreSQL consulting'),
('open_source', 'PostgreSQL training'),
('open_source', 'PostgreSQL 24x7 support'),
('closed_source', 'Oracle tuning'),
('closed_source', 'Oracle license management'),
('closed_source', 'IBM DB2 training');
GRANT ALL ON SCHEMA PUBLIC TO bob;
GRANT ALL ON TABLE t_service TO bob;
CREATE FUNCTION debug_me(text) RETURNS boolean AS
$$
BEGIN
RAISE NOTICE 'called as session_user=%, current_user=% for "%" ',
session_user, current_user, $1;
RETURN true;
END;
$$ LANGUAGE 'plpgsql';
GRANT ALL ON FUNCTION debug_me TO bob;
ALTER TABLE t_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY bob_pol ON t_service
FOR SELECT
TO bob
USING (debug_me(service));
SET ROLE bob;
explain analyze select * from t_service where service like 'Oracle%';
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL
consulting"
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL
training"
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL
24x7 support"
NOTICE:  called as session_user=postgres, current_user=bob for "Oracle
tuning"
NOTICE:  called as session_user=postgres, current_user=bob for "Oracle
license management"
NOTICE:  called as session_user=postgres, current_user=bob for "IBM DB2
training"
  QUERY PLAN

--
 Seq Scan on t_service  (cost=0.00..241.00 rows=1 width=64) (actual
time=0.294..0.391 rows=2 loops=1)
   Filter: (debug_me(service) AND (service ~~ 'Oracle%'::text))
   Rows Removed by Filter: 4
 Planning time: 0.112 ms
 Execution time: 0.430 ms
(5 rows)

explain analyze select * from t_service where
t_service.service_type='open_source';
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL
consulting"
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL
training"
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL
24x7 support"
  QUERY PLAN

--
 Seq Scan on t_service  (cost=0.00..241.00 rows=1 width=64) (actual
time=0.159..0.302 rows=3 loops=1)
   Filter: ((service_type = 'open_source'::text) AND debug_me(service))
   Rows Removed by Filter: 3
 Planning time: 0.129 ms
 Execution time: 0.348 ms
(5 rows)

Re: Row estimates for empty tables

[Christophe Pettus]

> On Jul 24, 2020, at 12:14, Pavel Stehule  wrote:
> 
> this application stores some results in tables (as guard against repeated 
> calculations). Lot of these tables have zero or one row. 

Yes, that's the situation we encountered, too.  It's not very common (and even 
less common, I would assume, that it results in a bad plan), but it did in this 
case.

--
-- Christophe Pettus
   x...@thebuild.com

Re: when is RLS policy applied

[Adrian Klaver]

On 7/24/20 12:34 PM, Ted Toth wrote:
I'm trying to understand when RLS select policy is applied so I created 
the follow to test but I don't understand why the query filter order is 
different for the 2 queries can anyone explain?


The way I see it is:

1) First case. The service column is the one the RLS is being applied 
against so the server needs to run through the service values to see if 
they test True or not(not knowing they will all return true) and then 
apply the like filter against the rows that the user is allowed to see.


2) Second case. The server filters out the service_type that do not 
apply to get a shortened list of rows that it then applies the USING 
function against the service values to test whether they are True or not.




CREATE USER bob NOSUPERUSER;
CREATE TABLE t_service (service_type text, service text);
INSERT INTO t_service VALUES
     ('open_source', 'PostgreSQL consulting'),
     ('open_source', 'PostgreSQL training'),
     ('open_source', 'PostgreSQL 24x7 support'),
     ('closed_source', 'Oracle tuning'),
     ('closed_source', 'Oracle license management'),
     ('closed_source', 'IBM DB2 training');
GRANT ALL ON SCHEMA PUBLIC TO bob;
GRANT ALL ON TABLE t_service TO bob;
CREATE FUNCTION debug_me(text) RETURNS boolean AS
$$
BEGIN
     RAISE NOTICE 'called as session_user=%, current_user=% for "%" ',
         session_user, current_user, $1;
     RETURN true;
END;
$$ LANGUAGE 'plpgsql';
GRANT ALL ON FUNCTION debug_me TO bob;
ALTER TABLE t_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY bob_pol ON t_service
     FOR SELECT
     TO bob
     USING (debug_me(service));
SET ROLE bob;
explain analyze select * from t_service where service like 'Oracle%';
NOTICE:  called as session_user=postgres, current_user=bob for 
"PostgreSQL consulting"
NOTICE:  called as session_user=postgres, current_user=bob for 
"PostgreSQL training"
NOTICE:  called as session_user=postgres, current_user=bob for 
"PostgreSQL 24x7 support"
NOTICE:  called as session_user=postgres, current_user=bob for "Oracle 
tuning"
NOTICE:  called as session_user=postgres, current_user=bob for "Oracle 
license management"
NOTICE:  called as session_user=postgres, current_user=bob for "IBM DB2 
training"

                                               QUERY PLAN
--
  Seq Scan on t_service  (cost=0.00..241.00 rows=1 width=64) (actual 
time=0.294..0.391 rows=2 loops=1)

    Filter: (debug_me(service) AND (service ~~ 'Oracle%'::text))
    Rows Removed by Filter: 4
  Planning time: 0.112 ms
  Execution time: 0.430 ms
(5 rows)

explain analyze select * from t_service where 
t_service.service_type='open_source';
NOTICE:  called as session_user=postgres, current_user=bob for 
"PostgreSQL consulting"
NOTICE:  called as session_user=postgres, current_user=bob for 
"PostgreSQL training"
NOTICE:  called as session_user=postgres, current_user=bob for 
"PostgreSQL 24x7 support"

                                               QUERY PLAN
--
  Seq Scan on t_service  (cost=0.00..241.00 rows=1 width=64) (actual 
time=0.159..0.302 rows=3 loops=1)

    Filter: ((service_type = 'open_source'::text) AND debug_me(service))
    Rows Removed by Filter: 3
  Planning time: 0.129 ms
  Execution time: 0.348 ms
(5 rows)



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: when is RLS policy applied

[Tom Lane]

Ted Toth  writes:
> I'm trying to understand when RLS select policy is applied so I created the
> follow to test but I don't understand why the query filter order is
> different for the 2 queries can anyone explain?

The core reason why not is that the ~~ operator isn't considered
leakproof.  Plain text equality is leakproof, so it's safe to evaluate
ahead of the RLS filter --- and we'd rather do so because the plpgsql
function is assumed to be much more expensive than a built-in operator.

(~~ isn't leakproof because it can throw errors that expose information
about the pattern argument.)

regards, tom lane

Re: when is RLS policy applied

[Ted Toth]

On Fri, Jul 24, 2020 at 3:15 PM Tom Lane  wrote:

> Ted Toth  writes:
> > I'm trying to understand when RLS select policy is applied so I created
> the
> > follow to test but I don't understand why the query filter order is
> > different for the 2 queries can anyone explain?
>
> The core reason why not is that the ~~ operator isn't considered
> leakproof.  Plain text equality is leakproof, so it's safe to evaluate
> ahead of the RLS filter --- and we'd rather do so because the plpgsql
> function is assumed to be much more expensive than a built-in operator.
>
> (~~ isn't leakproof because it can throw errors that expose information
> about the pattern argument.)
>
> regards, tom lane
>

Thanks for the explanation.

Ted

bad JIT decision

[Scott Ribe]

I have come across a case where PG 12 with default JIT settings makes a 
dramatically bad decision. PG11 without JIT, executes the query in <1ms, PG12 
with JIT takes 7s--and explain analyze attributes all that time to JIT. (The 
plan is the same on both 11 & 12, it's just the JIT.) 

It is a complex query, with joins to subqueries etc; there is a decent amount 
of data (~50M rows), and around 80 partitions (by date) on the main table. The 
particular query that I'm testing is intended as a sort of base case, in that 
it queries on a small set (4) of unique ids which will not match any rows, thus 
the complex bits never get executed, and this is reflected in the plan, where 
the innermost section is:

->  Index Scan using equities_rds_id on equities e0  (cost=0.42..33.74 rows=1 
width=37) (actual time=6751.892..6751.892 rows=0 loops=1)
   Index Cond: (rds_id = ANY ('{..., ..., ..., ...}'::uuid[]))
   Filter: (security_type = 'ETP'::text)
   Rows Removed by Filter: 4

And that is ultimately followed by a couple of sets of 80'ish scans of 
partitions, which show never executed, pretty much as expected since there are 
no rows left to check. The final bit is:

JIT:
  Functions: 683
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 86.439 ms, Inlining 21.994 ms, Optimization 3900.318 ms, 
Emission 2561.409 ms, Total 6570.161 ms

Now I think the query is not so complex that there could possibly be 683 
distinct functions. I think this count must be the result of a smaller number 
of functions created per-partition. I can understand how that would make sense, 
and some testing in which I added conditions that would restrict the matches to 
a single partition seem to bear it out (JIT reports 79 functions in that case).

Given the magnitude of the miss in using JIT here, I am wondering: is it 
possible that the planner does not properly take into account the cost of 
JIT'ing a function for multiple partitions? Or is it that the planner doesn't 
have enough info about the restrictiveness of conditions, and is therefore 
anticipating running the functions against a great many rows?

--
Scott Ribe
scott_r...@elevated-dev.com
https://www.linkedin.com/in/scottribe/

Re: Row estimates for empty tables

[Tom Lane]

Pavel Stehule  writes:
> pá 24. 7. 2020 v 16:38 odesílatel Christophe Pettus 
> napsal:
>> Since we already special-case parent tables for partition sets, would a
>> storage parameter that lets you either tell the planner "no, really, zero
>> is reasonable here" or sets a minimum number of rows to plan for be
>> reasonable?

> It is an issue for special, not typical applications (this situation is
> typical for some OLAP patterns)  - it is not too often - but some clean
> solution (instead hacking postgres) can be nice.

The core issue here is "how do we know whether the table is likely to stay
empty?".  I can think of a couple of more or less klugy solutions:

1. Arrange to send out a relcache inval when adding the first page to
a table, and then remove the planner hack for disbelieving relpages = 0.
I fear this'd be a mess from a system structural standpoint, but it might
work fairly transparently.

2. Establish the convention that vacuuming or analyzing an empty table
is what you do to tell the system that this state is going to persist.
That's more or less what the existing comments in plancat.c envision,
but we never made a definition for how the occurrence of that event
would be recorded in the catalogs, other than setting relpages > 0.
Rather than adding another pg_class column, I'm tempted to say that
vacuum/analyze should set relpages to a minimum of 1, even if the
relation has zero pages.  That does get the job done:

regression=# create table foo(f1 text);
CREATE TABLE
regression=# explain select * from foo;
   QUERY PLAN   

 Seq Scan on foo  (cost=0.00..23.60 rows=1360 width=32)
(1 row)

regression=# vacuum foo;  -- doesn't help
VACUUM
regression=# explain select * from foo;
   QUERY PLAN   

 Seq Scan on foo  (cost=0.00..23.60 rows=1360 width=32)
(1 row)
regression=# update pg_class set relpages = 1 where relname = 'foo';
UPDATE 1
regression=# explain select * from foo;
 QUERY PLAN 

 Seq Scan on foo  (cost=0.00..0.00 rows=1 width=32)
(1 row)

(We're still estimating one row, but that's as a result of different
decisions that I'm not nearly as willing to compromise on...)

regards, tom lane

Re: when is RLS policy applied

[Ted Toth]

I've looked for information on leakproofness of operators but haven't found
anything can you direct me to a source of this information?

On Fri, Jul 24, 2020 at 3:40 PM Ted Toth  wrote:

>
> On Fri, Jul 24, 2020 at 3:15 PM Tom Lane  wrote:
>
>> Ted Toth  writes:
>> > I'm trying to understand when RLS select policy is applied so I created
>> the
>> > follow to test but I don't understand why the query filter order is
>> > different for the 2 queries can anyone explain?
>>
>> The core reason why not is that the ~~ operator isn't considered
>> leakproof.  Plain text equality is leakproof, so it's safe to evaluate
>> ahead of the RLS filter --- and we'd rather do so because the plpgsql
>> function is assumed to be much more expensive than a built-in operator.
>>
>> (~~ isn't leakproof because it can throw errors that expose information
>> about the pattern argument.)
>>
>> regards, tom lane
>>
>
> Thanks for the explanation.
>
> Ted
>

Re: when is RLS policy applied

[Adrian Klaver]

On 7/24/20 2:12 PM, Ted Toth wrote:
I've looked for information on leakproofness of operators but haven't 
found anything can you direct me to a source of this information?


See here:

https://www.postgresql.org/docs/12/catalog-pg-proc.html

"proleakproof 	bool 	  	The function has no side effects. No information 
about the arguments is conveyed except via the return value. Any 
function that might throw an error depending on the values of its 
arguments is not leak-proof."


So as example:

select proname from pg_proc where proleakproof = 't';

select proname from pg_proc where proleakproof = 'f';



On Fri, Jul 24, 2020 at 3:40 PM Ted Toth > wrote:



On Fri, Jul 24, 2020 at 3:15 PM Tom Lane mailto:t...@sss.pgh.pa.us>> wrote:

Ted Toth mailto:txt...@gmail.com>> writes:
 > I'm trying to understand when RLS select policy is applied so
I created the
 > follow to test but I don't understand why the query filter
order is
 > different for the 2 queries can anyone explain?

The core reason why not is that the ~~ operator isn't considered
leakproof.  Plain text equality is leakproof, so it's safe to
evaluate
ahead of the RLS filter --- and we'd rather do so because the
plpgsql
function is assumed to be much more expensive than a built-in
operator.

(~~ isn't leakproof because it can throw errors that expose
information
about the pattern argument.)

                         regards, tom lane


Thanks for the explanation.

Ted




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: when is RLS policy applied

[Tom Lane]

Ted Toth  writes:
> I've looked for information on leakproofness of operators but haven't found
> anything can you direct me to a source of this information?

Operators per se don't have a leakproofness attribute; the function
underlying the operator is what has that property.

regards, tom lane

Re: when is RLS policy applied

[Adrian Klaver]

On 7/24/20 2:15 PM, Adrian Klaver wrote:

On 7/24/20 2:12 PM, Ted Toth wrote:
I've looked for information on leakproofness of operators but haven't 
found anything can you direct me to a source of this information?


See here:

https://www.postgresql.org/docs/12/catalog-pg-proc.html

"proleakproof bool   The function has no side effects. No 
information about the arguments is conveyed except via the return value. 
Any function that might throw an error depending on the values of its 
arguments is not leak-proof."


So as example:

select proname from pg_proc where proleakproof = 't';

select proname from pg_proc where proleakproof = 'f';


To update this per Tom's answer:

select oprname, proname from pg_proc join pg_operator on pg_proc.oid = 
pg_operator.oprcode  where proleakproof = 't';


select oprname, proname from pg_proc join pg_operator on pg_proc.oid = 
pg_operator.oprcode  where proleakproof = 'f';






On Fri, Jul 24, 2020 at 3:40 PM Ted Toth > wrote:



    On Fri, Jul 24, 2020 at 3:15 PM Tom Lane mailto:t...@sss.pgh.pa.us>> wrote:

    Ted Toth mailto:txt...@gmail.com>> writes:
 > I'm trying to understand when RLS select policy is applied so
    I created the
 > follow to test but I don't understand why the query filter
    order is
 > different for the 2 queries can anyone explain?

    The core reason why not is that the ~~ operator isn't considered
    leakproof.  Plain text equality is leakproof, so it's safe to
    evaluate
    ahead of the RLS filter --- and we'd rather do so because the
    plpgsql
    function is assumed to be much more expensive than a built-in
    operator.

    (~~ isn't leakproof because it can throw errors that expose
    information
    about the pattern argument.)

                         regards, tom lane


    Thanks for the explanation.

    Ted







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Row estimates for empty tables

[Christophe Pettus]

> On Jul 24, 2020, at 14:09, Tom Lane  wrote:
> Rather than adding another pg_class column, I'm tempted to say that
> vacuum/analyze should set relpages to a minimum of 1, even if the
> relation has zero pages. 

If there's not an issue about relpages != actual pages on disk, that certain 
seems straight-forward, and no *more* hacky than the current situation.

--
-- Christophe Pettus
   x...@thebuild.com

Re: bad JIT decision

[David Rowley]

On Sat, 25 Jul 2020 at 08:46, Scott Ribe  wrote:
> Given the magnitude of the miss in using JIT here, I am wondering: is it 
> possible that the planner does not properly take into account the cost of 
> JIT'ing a function for multiple partitions? Or is it that the planner doesn't 
> have enough info about the restrictiveness of conditions, and is therefore 
> anticipating running the functions against a great many rows?

It does not really take into account the cost of jitting. If the total
plan cost is above the jit threshold then jit is enabled. If not, then
it's disabled.

There are various levels of jit and various thresholds that can be tweaked, see:

select name,setting from pg_settings where name like '%jit%';

But as far as each threshold goes, you either reach it or you don't.
Maybe that can be made better by considering jit in a more cost-based
way rather than by threshold, that way it might be possible to
consider jit per plan node rather than on the query as a whole. e.g,
if you have 1000 partitions and 999 of them have 1 row and the final
one has 1 billion rows, then it's likely a waste of time to jit
expressions for the 999 partitions.

However, for now, you might just want to try raising various jit
thresholds so that it only is enabled for more expensive plans.

David

Re: bad JIT decision

[Scott Ribe]

> On Jul 24, 2020, at 4:26 PM, David Rowley  wrote:
> 
> It does not really take into account the cost of jitting.

That is what I was missing.

I read about JIT when 12 was pre-release; in re-reading after my post I see 
that it does not attempt to estimate JIT cost. And in thinking about it, I 
realize that would be next to impossible to anticipate how expensive LLVM 
optimizstion was going to be.

In the case where a set of functions is replicated across partitions, it would 
be possible to do them once, then project the cost of the copies. Perhaps for 
PG 14 as better support for the combination of JIT optimization and 
highly-partitioned data ;-)

Re: bad JIT decision

[Tom Lane]

David Rowley  writes:
> However, for now, you might just want to try raising various jit
> thresholds so that it only is enabled for more expensive plans.

Yeah.  I'm fairly convinced that the v12 defaults are far too low,
because we are constantly seeing complaints of this sort.

regards, tom lane

Re: bad JIT decision

[David Rowley]

On Sat, 25 Jul 2020 at 10:37, Tom Lane  wrote:
>
> David Rowley  writes:
> > However, for now, you might just want to try raising various jit
> > thresholds so that it only is enabled for more expensive plans.
>
> Yeah.  I'm fairly convinced that the v12 defaults are far too low,
> because we are constantly seeing complaints of this sort.

I think plan cost overestimation is a common cause of unwanted jit too.

It would be good to see the EXPLAIN ANALYZE so we knew if that was the
case here.

David

Re: bad JIT decision

[Scott Ribe]

> On Jul 24, 2020, at 4:37 PM, Tom Lane  wrote:
> 
> Yeah.  I'm fairly convinced that the v12 defaults are far too low,
> because we are constantly seeing complaints of this sort.


They are certainly too low for our case; not sure if for folks who are not 
partitioning if they're way too low.

The passive-aggressive approach would really not be good general advice for 
you, but I'm actually glad that in our case they were low enough to get our 
attention early ;-)

I think I will disable optimization, because with our partitioning scheme we 
will commonly see blow ups of optimization time like this one.

The inlining time in this case is still much more than the query, but it is low 
enough to not be noticed by users, and I think that with different variations 
of the parameters coming in to the query, that for the slower versions (more 
partitions requiring actual scans), inlining will help. Slowing down the 
fastest while speeding up the slower is a trade off we can take.

is JIT available

[Scott Ribe]

So JIT is enabled in your conf, how can you tell from within a client session 
whether it's actually available (PG compiled with it and compiler available)?

(In the other discussion I started, doing a dump and import of just the tables 
involved, onto a system where JIT was inadvertently not working for some 
reason, lead me down a dead end for a bit.)

--
Scott Ribe
scott_r...@elevated-dev.com
https://www.linkedin.com/in/scottribe/

Re: bad JIT decision

[Andres Freund]

Hi,

On 2020-07-24 18:37:02 -0400, Tom Lane wrote:
> David Rowley  writes:
> > However, for now, you might just want to try raising various jit
> > thresholds so that it only is enabled for more expensive plans.
> 
> Yeah.  I'm fairly convinced that the v12 defaults are far too low,
> because we are constantly seeing complaints of this sort.

I think the issue is more that we need to take into accoutn that the
overhead of JITing scales ~linearly with the number of JITed
expressions. And that's not done right now.  I've had a patch somewhere
that had a prototype implementation of changing the costing to be
#expressions * some_cost, and I think that's a lot more accurate.

Greetings,

Andres Freund

Re: bad JIT decision

[Andres Freund]

Hi,

On Fri, Jul 24, 2020, at 15:32, Scott Ribe wrote:
> > On Jul 24, 2020, at 4:26 PM, David Rowley  wrote:
> > 
> > It does not really take into account the cost of jitting.
> 
> That is what I was missing.
> 
> I read about JIT when 12 was pre-release; in re-reading after my post I 
> see that it does not attempt to estimate JIT cost. And in thinking 
> about it, I realize that would be next to impossible to anticipate how 
> expensive LLVM optimizstion was going to be.

We certainly can do better than now.

> In the case where a set of functions is replicated across partitions, 
> it would be possible to do them once, then project the cost of the 
> copies. 

Probably not - JITing functions separately is more expensive than doing them 
once... The bigger benefit there is to avoid optimizing functions that are 
likely to be the same.

> Perhaps for PG 14 as better support for the combination of JIT 
> optimization and highly-partitioned data ;-)

If I posted a few patches to test / address some of these issue, could you test 
them with your schema & querries?

Regards,

Andres

Re: is JIT available

[Pavel Stehule]

so 25. 7. 2020 v 0:49 odesílatel Scott Ribe 
napsal:

> So JIT is enabled in your conf, how can you tell from within a client
> session whether it's actually available (PG compiled with it and compiler
> available)?
>
> (In the other discussion I started, doing a dump and import of just the
> tables involved, onto a system where JIT was inadvertently not working for
> some reason, lead me down a dead end for a bit.)
>

SELECT * FROM pg_config;

Regards

Pavel


> --
> Scott Ribe
> scott_r...@elevated-dev.com
> https://www.linkedin.com/in/scottribe/
>
>
>
>
>
>

How does vacuum works in postgresql

[Rama Krishnan]

Hi Experts,

I read this tutorials blog on edb it says vacuum has three phases
1.ingest phase
2.pruning phase
3.cleaning phase

Apart from this blog, no one has explained about the vacuum working


I m getting confusion on each can anyone explain me

https://www.enterprisedb.com/postgres-tutorials/how-does-vacuum-work-postgresql


Thanks& Regards
RK

Re: bad JIT decision

[David Rowley]

On Sat, 25 Jul 2020 at 10:42, David Rowley  wrote:
>
> On Sat, 25 Jul 2020 at 10:37, Tom Lane  wrote:
> >
> > David Rowley  writes:
> > > However, for now, you might just want to try raising various jit
> > > thresholds so that it only is enabled for more expensive plans.
> >
> > Yeah.  I'm fairly convinced that the v12 defaults are far too low,
> > because we are constantly seeing complaints of this sort.
>
> I think plan cost overestimation is a common cause of unwanted jit too.
>
> It would be good to see the EXPLAIN ANALYZE so we knew if that was the
> case here.

So Scott did send me the full EXPLAIN ANALYZE for this privately. He
wishes to keep the full output private.

After looking at it, it seems the portion that he pasted above, aka:

->  Index Scan using equities_rds_id on equities e0  (cost=0.42..33.74
rows=1 width=37) (actual time=6751.892..6751.892 rows=0 loops=1)
   Index Cond: (rds_id = ANY ('{..., ..., ..., ...}'::uuid[]))
   Filter: (security_type = 'ETP'::text)
   Rows Removed by Filter: 4

Is nested at the bottom level join, about 6 joins deep.  The lack of
any row being found results in upper level joins not having to do
anything, and the majority of the plan is (never executed).

David