[kdepim-users] akonadi fsck fails

[J. Roeleveld]

On Wednesday, August 26, 2015 05:23:31 PM Pablo Sanchez wrote:
> [ Comments below, in-line ]
> 
> On 08/26/2015 03:19 PM, J. Roeleveld wrote:
> > 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);
> 
> Hi Joost,
> 
> Yes, you're right, the above is superior to the NOT EXIST.  The NOT
> EXIST's Execution Plan shows it will perform a full table scan (Seq
> Scan) of [pimitemflagrelation].

Actually, further testing shows that "NOT EXISTS" is actually faster and 
cheaper.
I copied the tables to a different machine and performed a full reboot between 
tests. (overkill, but only way to get a clean baseline).

The NOT EXISTS version was faster. (average around 600ms, the other one 
averages around 800ms)


> I find NOT EXIST to nearly always be non-performant across all RDBMS's.

In one of my projects we did extensive testing on Oracle databases, and the 
NOT EXISTS and EXISTS versions were in 99% of the cases faster.

> Too bad the LEFT OUTER JOIN doesn't work in the FROM for PG.

I'm not convinced that would actually be faster.
Then again, how would you see the DELETE function?
There is logic in the assumption that data would be deleted from both tables 
(if the " IS NULL " wouldn't be in the WHERE)

Kind regards,

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