[kdepim-users] akonadi fsck fails

[J. Roeleveld]

On Wednesday, August 26, 2015 09:19:58 PM J. Roeleveld wrote:
> 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

Actually, rereading the results, the "where not exist" is the one with the 
lowest total cost: 78179.02

The other one has: 244683.55

The subselect causes a temp-table (materialize) to be created.

--
Joost
_______________________________________________
KDE PIM users mailing list
Subscription management: https://mail.kde.org/mailman/listinfo/kdepim-users