Performance Regression (article purge)

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
AngryChris
Bear Rating Master
Bear Rating Master
Posts: 135
Joined: 08 Apr 2013, 02:42

Performance Regression (article purge)

Postby AngryChris » 15 Jul 2015, 01:55

Last edited by AngryChris on 15 Jul 2015, 04:55, edited 2 times in total.

AngryChris
Bear Rating Master
Bear Rating Master
Posts: 135
Joined: 08 Apr 2013, 02:42

Re: Performance Regression (article purge)

Postby AngryChris » 15 Jul 2015, 01:56


User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Performance Regression (article purge)

Postby fox » 15 Jul 2015, 08:31

i had my doubts. maybe it's faster only on mysql, who knows. i'll revert it.

e: the whole BREAKTHROUGH!!11 thing was p funny though

virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: Performance Regression (article purge)

Postby virgo » 15 Jul 2015, 12:33

I don't know much of PostgreSQL, but in Firebird NOT IN query is one of the worst things to do (because IN is run for every record in main table). But looking at the original query, you probably could get rid of COUNT by making it NOT EXISTS query instead of NOT IN.
So original:

DELETE FROM ttrss_entries WHERE
(SELECT COUNT(int_id) FROM ttrss_user_entries WHERE ref_id = id) = 0

could be rewritten

DELETE FROM ttrss_entries E WHERE
NOT EXISTS (SELECT U.ref_id FROM ttrss_user_entries U WHERE U.ref_id = E.id)

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Performance Regression (article purge)

Postby fox » 15 Jul 2015, 12:48


virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: Performance Regression (article purge)

Postby virgo » 15 Jul 2015, 13:09

It seems, that mysql does not support table aliases in DELETE.
DELETE FROM ttrss_entries WHERE
NOT EXISTS (SELECT ref_id FROM ttrss_user_entries WHERE ref_id = id)
seems to work.
But I have no idea, why rows would be different. Because COUNT() = 0 should be when there is no record. I'm probably missing something.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Performance Regression (article purge)

Postby fox » 15 Jul 2015, 13:24

oh this is the amount of lines returned by the analyze query, duh

i like your query, i think it's an improvement

also i had no idea you can use this shorter form for aliases, cool

e: merged, let's see how this goes

vidar
Bear Rating Trainee
Bear Rating Trainee
Posts: 13
Joined: 06 May 2013, 00:19

Re: Performance Regression (article purge)

Postby vidar » 15 Jul 2015, 18:47


User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Performance Regression (article purge)

Postby sleeper_service » 15 Jul 2015, 21:39

in case anybody doesn't know about this, http://explain.depesz.com/ it was suggested by a PG friend last night to make explain analyze output a bit more readable :)


Return to “Support”

Who is online

Users browsing this forum: No registered users and 8 guests