[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