[kdepim-users] akonadi fsck fails
J. Roeleveld
joost at antarean.org
Wed Aug 26 20:19:58 BST 2015
On Wednesday, August 26, 2015 07:01:48 PM J. Roeleveld wrote:
> On 26 August 2015 18:45:27 CEST, Pablo Sanchez <pablo at blueoakdb.com> wrote:
> >[ Comments below, in-line ]
> >
> >On 08/26/2015 12:23 PM, J. Roeleveld wrote:
> >>> DELETE FROM pimitemflagrelation
> >>>
> >>> > LEFT JOIN pimitemtable
> >>> >
> >>> > ON pimitemflagrelation.pimitem_id =
> >
> >pimitemtable.id
> >
> >>> > WHERE pimitemtable.id IS NULL;
> >>> >
> >>> >I'm not sure whether MySQL supports a LEFT JOIN in the DELETE but
> >
> >you
> >
> >>> >should be able to give it a whirl. :)
> >>
> >> Personally, I don't care about MySQL. So someone who uses that will
> >
> >need to test it.
> >
> >> Can that be easily built using QueryBuilder?
> >>
> >> I will try this week (end) and get back on this.
> >>
> >> --
> >> Joost
> >
> >Hi Joost,
> >
> >Are you using Postgres? If so, once it works, please try running an
> >EXPLAIN and send me the output. We can see what PG thinks.
> >
> >What would be awesome is if you ran an EXPLAIN, saved the results, then
> >
> >run an ANALYZE on the two objects: [pimitemflagrelation] and
> >[pimitemtable]. Then re-do the EXPLAIN.
>
> Yes, running a standalone server. Found that performs better compared to
> running it started by akonadi. Also means I only run 1 instance. (Use that
> for development work as well)
>
> I will do the explains later today or tomorrow. Don't have my laptop with me
> atm.
>
> --
> Joost
Ok, the results are as attached.
(Copied directly from my psql-session)
The one with the LEFT JOIN doesn't work.
For the other ones, there is, in the plan, a big difference between the two.
Just like you said in your original email.
Based on that, the following one is the best option:
++++
delete from pimitemflagrelation
where pimitemflagrelation.pimitem_id in (
select pimitem_id
from pimitemflagrelation
left join pimitemtable
on pimitemflagrelation.pimitem_id = pimitemtable.id);
++++
As for splitting it all up. If it has to be in a single transaction, how do I
start a transaction using the objects present?
I usually write the query into the code and send that to the database. I'm not
familiar with the querybuilder objects being used in akonadi.
Is there a direct link to some documentation about those?
Kind regards,
Joost Roeleveld
-------------- next part --------------
joost-akonadi=> explain
joost-akonadi-> delete from pimitemflagrelation
joost-akonadi-> where pimitemflagrelation.pimitem_id in (
joost-akonadi(> select pimitem_id
joost-akonadi(> from pimitemflagrelation
joost-akonadi(> left join pimitemtable
joost-akonadi(> on pimitemflagrelation.pimitem_id = pimitemtable.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on pimitemflagrelation (cost=1.28..247913.92 rows=1348722 width=18)
-> Merge Semi Join (cost=1.28..247913.92 rows=1348722 width=18)
Merge Cond: (pimitemflagrelation.pimitem_id = pimitemflagrelation_1.pimitem_id)
-> Index Scan using pimitemflagrelation_pkey on pimitemflagrelation (cost=0.43..69913.96 rows=1348722 width=14)
-> Materialize (cost=0.85..149759.38 rows=1348722 width=20)
-> Merge Left Join (cost=0.85..146387.58 rows=1348722 width=20)
Merge Cond: (pimitemflagrelation_1.pimitem_id = pimitemtable.id)
-> Index Scan using pimitemflagrelation_pkey on pimitemflagrelation pimitemflagrelation_1 (cost=0.43..69913.96 rows=1348722 width=14)
-> Materialize (cost=0.42..65454.86 rows=541566 width=10)
-> Index Scan using pimitemtable_pkey on pimitemtable (cost=0.42..64100.94 rows=541566 width=10)
(10 rows)
joost-akonadi=> explain
joost-akonadi-> delete from pimitemflagrelation
joost-akonadi-> where not exists
joost-akonadi-> (select 1 from pimitemtable where pimitemtable.id =
joost-akonadi(> pimitemflagrelation.pimitem_id);
QUERY PLAN
------------------------------------------------------------------------------------------
Delete on pimitemflagrelation (cost=28398.24..79169.83 rows=738356 width=12)
-> Hash Anti Join (cost=28398.24..79169.83 rows=738356 width=12)
Hash Cond: (pimitemflagrelation.pimitem_id = pimitemtable.id)
-> Seq Scan on pimitemflagrelation (cost=0.00..20778.22 rows=1348722 width=14)
-> Hash (cost=18983.66..18983.66 rows=541566 width=10)
-> Seq Scan on pimitemtable (cost=0.00..18983.66 rows=541566 width=10)
(6 rows)
joost-akonadi=> explain
joost-akonadi-> DELETE FROM pimitemflagrelation
joost-akonadi-> LEFT JOIN pimitemtable
joost-akonadi-> ON pimitemflagrelation.pimitem_id = pimitemtable.id
joost-akonadi-> WHERE pimitemtable.id IS NULL;
ERROR: syntax error at or near "LEFT"
LINE 3: LEFT JOIN pimitemtable
joost-akonadi=> analyze pimitemflagrelation;
ANALYZE
joost-akonadi=> analyze pimitemtable;
ANALYZE
joost-akonadi=> explain
joost-akonadi-> delete from pimitemflagrelation
joost-akonadi-> where pimitemflagrelation.pimitem_id in (
joost-akonadi(> select pimitem_id
joost-akonadi(> from pimitemflagrelation
joost-akonadi(> left join pimitemtable
joost-akonadi(> on pimitemflagrelation.pimitem_id = pimitemtable.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on pimitemflagrelation (cost=1.28..244683.55 rows=1328312 width=18)
-> Merge Semi Join (cost=1.28..244683.55 rows=1328312 width=18)
Merge Cond: (pimitemflagrelation.pimitem_id = pimitemflagrelation_1.pimitem_id)
-> Index Scan using pimitemflagrelation_pkey on pimitemflagrelation (cost=0.43..68884.89 rows=1328312 width=14)
-> Materialize (cost=0.85..148957.57 rows=1328312 width=20)
-> Merge Left Join (cost=0.85..145636.79 rows=1328312 width=20)
Merge Cond: (pimitemflagrelation_1.pimitem_id = pimitemtable.id)
-> Index Scan using pimitemflagrelation_pkey on pimitemflagrelation pimitemflagrelation_1 (cost=0.43..68884.89 rows=1328312 width=14)
-> Materialize (cost=0.42..65937.84 rows=528141 width=10)
-> Index Scan using pimitemtable_pkey on pimitemtable (cost=0.42..64617.49 rows=528141 width=10)
(10 rows)
joost-akonadi=> explain
joost-akonadi-> delete from pimitemflagrelation
joost-akonadi-> where not exists
joost-akonadi-> (select 1 from pimitemtable where pimitemtable.id =
joost-akonadi(> pimitemflagrelation.pimitem_id);
QUERY PLAN
------------------------------------------------------------------------------------------
Delete on pimitemflagrelation (cost=28030.17..78179.02 rows=738434 width=12)
-> Hash Anti Join (cost=28030.17..78179.02 rows=738434 width=12)
Hash Cond: (pimitemflagrelation.pimitem_id = pimitemtable.id)
-> Seq Scan on pimitemflagrelation (cost=0.00..20574.12 rows=1328312 width=14)
-> Hash (cost=18849.41..18849.41 rows=528141 width=10)
-> Seq Scan on pimitemtable (cost=0.00..18849.41 rows=528141 width=10)
(6 rows)
-------------- next part --------------
_______________________________________________
KDE PIM users mailing list
Subscription management: https://mail.kde.org/mailman/listinfo/kdepim-users
More information about the kdepim-users
mailing list