Re: akonadi fsck fails

"J. Roeleveld" <[email protected]>
Newsgroups gmane.comp.kde.users.pim
Message-ID <2937847.oaLG5yxqmC@andromeda> 
On Wednesday, August 26, 2015 07:01:48 PM J. Roeleveld wrote:
> On 26 August 2015 18:45:27 CEST, Pablo Sanchez <[email protected]> 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

_______________________________________________
KDE PIM users mailing list
Subscription management: https://mail.kde.org/mailman/listinfo/kdepim-users
ExplainPlans_Delete_Statements.txt (text/plain, 5.4 KB) 
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)
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.