> On Jun 29, 2021, at 11:02 AM, Ron wrote:
>
> What's an IOS?
An Index Only Scan. See
https://www.postgresql.org/docs/14/indexes-index-only-scans.html
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 6/29/21 12:46 PM, Tom Lane wrote:
Ron writes:
On 6/29/21 11:42 AM, Tom Lane wrote:
If there's a significant difference in relallvisible fractions, that
would point to something different in your VACUUM housekeeping on
the two systems.
Prod is brand new. Loaded on Saturday; we saw this
> On Jun 29, 2021, at 10:33 AM, Ron wrote:
>
> Prod is brand new. Loaded on Saturday; we saw this problem on Sunday during
> pre-acceptance. Thus, while running ANALYZE was top of the list of Things To
> Do, running VACUUM was low.
>
> Is that a mistaken belief?
You might want to run
Ron writes:
> On 6/29/21 11:42 AM, Tom Lane wrote:
>> If there's a significant difference in relallvisible fractions, that
>> would point to something different in your VACUUM housekeeping on
>> the two systems.
> Prod is brand new. Loaded on Saturday; we saw this problem on Sunday during
>
On 6/29/21 11:42 AM, Tom Lane wrote:
Ron writes:
On 6/29/21 10:41 AM, Michael Lewis wrote:
What's an example query that uses indexes on test and does not on live?
SELECT COUNT(*) FROM sep_info_report_extract;
On prod, there's a list of "Parallel Seq Scan on _partname" records in
the
Ron writes:
> On 6/29/21 10:41 AM, Michael Lewis wrote:
>> What's an example query that uses indexes on test and does not on live?
> SELECT COUNT(*) FROM sep_info_report_extract;
> On prod, there's a list of "Parallel Seq Scan on _partname" records in
> the EXPLAIN output, while the test
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
On 6/29/21 10:41 AM, Michael Lewis wrote:
Are vacuum and analyze happening regularly on the live system?
Yes. There's a nightly cron job which vacuums those it thinks needs it
(though it's INSERT-heavy), and ditto ANALYZE.
Specifically, I ran ANALYZE on the prod table just before running
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
Postgresql 12.5
I've got a big (about 50M rows, but 1.4TB because of xml attachments)
partitioned table full of data that we're seeing sequential scans on, even
though there are supporting indices. Will adding CHECK constraints on the
children, which match the partition ranges influence
Thanks Laurenz
This is interesting...b is True
Thanks and regards,
Jitendra
On Wed 11 Nov, 2020, 22:52 Laurenz Albe, wrote:
> On Wed, 2020-11-11 at 11:47 +0100, Tomas Vondra wrote:
> > you may do this, for example:
> >
> > (b it not null and b = true) and (c is not null)
> >
> > Or
On Wed, 2020-11-11 at 11:47 +0100, Tomas Vondra wrote:
> you may do this, for example:
>
> (b it not null and b = true) and (c is not null)
>
> Or something like that.
My (equivalent) suggestion:
b IS TRUE AND c IS NOT NULL
Yours,
Laurenz Albe
--
Cybertec |
Thanks Tomas
Understood... My bad Was just not looking at that aspect
Thanks once again,
Regards,
Jitendra
On Wed, 11 Nov 2020 at 16:17, Tomas Vondra
wrote:
>
> On 11/11/20 10:06 AM, Jitendra Loyal wrote:
> > Thanks Nikolay
> >
> > I read that but is there a way to meet the above
On 11/11/20 10:06 AM, Jitendra Loyal wrote:
> Thanks Nikolay
>
> I read that but is there a way to meet the above requirement. And I will
> like to add that IS NULL and IS NOT NULL should evaluate to true/false.
> These operators are made for this and should not be returning NULL.
>
This has
ll, false);
>>
>> This behavior is described in the docs
>> https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS:
>>
>> > It should be noted that a check constraint is satisfied if the check
>> > expression evaluates
ps://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS
> :
>
> > It should be noted that a check constraint is satisfied if the check
> expression evaluates to true or the null value. Since most expressions will
> evaluate to the null valu
rrent/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS
:
> It should be noted that a check constraint is satisfied if the check
expression evaluates to true or the null value. Since most expressions will
evaluate to the null value if any operand is null, they will not prevent
null values in the constra
st 11. 11. 2020 v 9:38 odesílatel Chris Sterritt
napsal:
>
> On 11/11/2020 06:44, Jitendra Loyal wrote:
>
> Consider this table definition:
> create table t ( i serial, b bool, c bool,
> constraint b_c check ( (b = true and c is
> not null ) or (b is
On 11/11/2020 06:44, Jitendra Loyal wrote:
Consider this table definition:
create table t ( i serial, b bool, c bool,
constraint b_c check ( (b = true and c is not
null ) or (b is distinct
from true and c is null) )
constraint b_c check ( (b = true
Consider this table definition:
create table t ( i serial, b bool, c bool,
constraint b_c check ( (b = true and c is
not null ) or (b is distinct
from true and c is null) )
constraint b_c check ( (b = true and c is
not null )
Dinko Papak writes:
> Here are 3 interesting (to me) numbers:
> 1. creating index on expression (func(timestamp)) takes 5 seconds
> 2. creating check constraint on the same expression takes 10 seconds
> 3. adding partition table based on the same expression without check
> expression takes 20
Here are 3 interesting (to me) numbers:
1. creating index on expression (func(timestamp)) takes 5 seconds
2. creating check constraint on the same expression takes 10 seconds
3. adding partition table based on the same expression without check expression
takes 20 seconds (this has been
On 27/03/18 15:44, Paul Jungwirth wrote:
> SELECT c.conname,
> ...
This just does a variation on select * from
information_schema.check_constraints, and has the same issue, that is the the
returned value for the constraint is not what I give when I create it - but
some 'normalised'
On 03/27/2018 04:23 AM, Steve Rogerson wrote:
I am trying to add/change a constraint programmatically, but not if it
already exists, and is the same as before.
...
Is there a way of getting the "normalised" version of constraint so decide if
I need to update the constraint if one already exists?
williamI am trying to add/change a constraint programmatically, but not if it
already exists, and is the same as before.
I can so something like (may not be precise ...)
select check_clause from information_schema.check_constraints where
constraint_name = 'my-name'
but this returns that clause
25 matches
Mail list logo