On Tue, Jun 16, 2020 at 11:49:15AM +0200, Koen De Groote wrote:
> Alright, I've done that, and that seems to be a very good result: https://
> explain.depesz.com/s/xIph
>
> The method I ended up using:
>
> create or replace function still_needs_backup(shouldbebackedup bool,
> backupperformed
Alright, I've done that, and that seems to be a very good result:
https://explain.depesz.com/s/xIph
The method I ended up using:
create or replace function still_needs_backup(shouldbebackedup bool,
backupperformed bool)
returns BOOLEAN as $$
select $1 AND NOT $2;
$$
language sql immutable;
On Tue, Jun 9, 2020 at 6:24 AM Koen De Groote wrote:
> Right. In that case, the function I ended up with is this:
>
> create or replace function still_needs_backup(bool, bool)
> returns BOOLEAN as $$
> BEGIN
> PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2;
> IF
On Tue, Jun 9, 2020 at 02:23:51PM +0200, Koen De Groote wrote:
> Right. In that case, the function I ended up with is this:
>
> create or replace function still_needs_backup(bool, bool)
> returns BOOLEAN as $$
> BEGIN
> PERFORM 1 from item where shouldbebackedup=$1 and
Right. In that case, the function I ended up with is this:
create or replace function still_needs_backup(bool, bool)
returns BOOLEAN as $$
BEGIN
PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2;
IF FOUND THEN
RETURN TRUE;
ELSE
RETURN FALSE;
On Mon, Jun 8, 2020 at 2:34 PM Koen De Groote wrote:
> So, this query:
>
> select * from item where shouldbebackedup=true and
> itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by
> filepath asc, id asc limit 100 offset 10400;
>
> Was made into a function:
>
> create or
So, this query:
select * from item where shouldbebackedup=true and itemCreated<='2020-06-05
00:00:00.000' and backupperformed=false order by filepath asc, id asc limit
100 offset 10400;
Was made into a function:
create or replace function NeedsBackup(text, int, int default 100)
returns setof
I'll attempt this next week.
On Fri, Jun 5, 2020, 21:11 Michael Lewis wrote:
> Those row estimates are pretty far off.
>
> Standard indexes and partial indexes don't get custom statistics created
> on them, but functional indexes do. I wonder if a small function
> needs_backup(
Those row estimates are pretty far off.
Standard indexes and partial indexes don't get custom statistics created on
them, but functional indexes do. I wonder if a small function needs_backup(
shouldbebackedup, backupperformed ) and an index created on that function
would nicely alleviate the
The requested result: https://explain.depesz.com/s/G7mU
Also, the data from the statistic itself:
=> SELECT stxname, stxkeys, stxdependencies
-> FROM pg_statistic_ext
-> WHERE stxname = 's1';
stxname | stxkeys | stxdependencies
-+-+-
s1 | 29 35 |
On 6/5/20 7:05 AM, Koen De Groote wrote:
I've collected all relevant info(I think so at least) and put it here:
The table in question is used to keep filepath data, of files on a
harddrive.
The query in question is used to retrieve items which should be backed
up, but have not yet been.
The
I've collected all relevant info(I think so at least) and put it here:
The table in question is used to keep filepath data, of files on a
harddrive.
The query in question is used to retrieve items which should be backed up,
but have not yet been.
The relevant columns of the table:
Adrian Klaver writes:
> On 6/4/20 9:43 AM, Tom Lane wrote:
>> It's possible that the index had bloated to the point where the planner
>> thought it was cheaper to use a seqscan. Did you make a note of the
>> cost estimates for the different plans?
> I missed the part where the OP pointed to a
On 6/4/20 9:43 AM, Tom Lane wrote:
Koen De Groote writes:
I've got a table with about 30 million rows and a particular index that up
until recently was actively being used.
And then it stopped being used and the query that the index was made for,
is now doing sequential scans.
Deleting the
Koen De Groote writes:
> I've got a table with about 30 million rows and a particular index that up
> until recently was actively being used.
> And then it stopped being used and the query that the index was made for,
> is now doing sequential scans.
> Deleting the index and creating it again,
On 6/4/20 8:37 AM, Koen De Groote wrote:
Greetings,
The following is using version 11.2 of PostgreSQL.
I've got a table with about 30 million rows and a particular index that
up until recently was actively being used.
And then it stopped being used and the query that the index was made
Greetings,
The following is using version 11.2 of PostgreSQL.
I've got a table with about 30 million rows and a particular index that up
until recently was actively being used.
And then it stopped being used and the query that the index was made for,
is now doing sequential scans.
Deleting the
17 matches
Mail list logo
