Adding indexes to tag table to speed things up

[Robert Lyon]

To make requests to tags table faster

[Robert Lyon (robertl-9)]

A test just adding an index to resourcetype does help

However the uniqueness of a tag is based on the following fields
 tag, resourcetype, resourceid, ownertype, ownerid

So we could add an index / unique index on those as a set

[Robert Lyon (robertl-9)]

To analyze do something like this:

explain analyze SELECT a.title, p.title AS parenttitle, a.id, a.parent, a.owner, a.author, a.authorname,
                    a.description, a.allowcomments, at.tag, a.ctime, a.mtime
                FROM "artefact" a
                JOIN "artefact" p ON a.parent = p.id
                JOIN "artefact_blog_blogpost" ab ON (ab.blogpost = a.id AND ab.published = 1)
                JOIN "tag" at ON (at.resourcetype = 'artefact' AND at.resourceid = a.id::varchar)
                WHERE a.artefacttype = 'blogpost'
                AND a.owner = (SELECT "owner" from "view" WHERE id = 123) AND EXISTS (
                        SELECT * FROM "tag" AS at
                        WHERE at.resourcetype = 'artefact' AND at.resourceid = a.id::varchar
                        AND at.tag = 'test'
                    ) ORDER BY a.ctime DESC, a.id DESC;

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/11096

[Robert Lyon (robertl-9)]

To test:

Go to a big site and find a view with some tags on it - make a note of the view id and plug that into the SQL above in place of the '123'

Run that 'explain ...' sql and make a note of the "Planning time" and "Execution time" - run it a few time to get an average

Then add the unique index
ALTER TABLE tag ADD CONSTRAINT tag_unique UNIQUE (tag, resourcetype, resourceid, ownertype, ownerid);

Run that 'explain ...' sql and make a note of the "Planning time" and "Execution time" - run it a few time to get an average

It should be way faster

Then drop the index so you can add it back with upgrade of the site

ALTER TABLE tag
DROP CONSTRAINT tag_unique;

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/11096
Committed: https://git.mahara.org/mahara/mahara/commit/210227db220bb32015b3e5cfdc4727527b04f68c
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: master

commit 210227db220bb32015b3e5cfdc4727527b04f68c
Author: Robert Lyon <email address hidden>
Date: Thu Jul 30 15:15:35 2020 +1200

Bug 1889340: Adding new index to 'tag' table

So that queries to the table go faster

Change-Id: I8313e37dc50b923610f1d02375fe402e64939d79
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Patch for "20.04_STABLE" branch: https://reviews.mahara.org/11271

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/11271
Committed: https://git.mahara.org/mahara/mahara/commit/8ac1f9b53e74951f74e12ad2fe58e86a58a7e5ae
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: 20.04_STABLE

commit 8ac1f9b53e74951f74e12ad2fe58e86a58a7e5ae
Author: Robert Lyon <email address hidden>
Date: Thu Jul 30 15:15:35 2020 +1200

Bug 1889340: Adding new index to 'tag' table

So that queries to the table go faster

Change-Id: I8313e37dc50b923610f1d02375fe402e64939d79
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Patch for "19.10_STABLE" branch: https://reviews.mahara.org/11272

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/11272
Committed: https://git.mahara.org/mahara/mahara/commit/35f0967ac5aad5dea3ccd77f7ade747765ea2eca
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: 19.10_STABLE

commit 35f0967ac5aad5dea3ccd77f7ade747765ea2eca
Author: Robert Lyon <email address hidden>
Date: Thu Jul 30 15:15:35 2020 +1200

Bug 1889340: Adding new index to 'tag' table

So that queries to the table go faster

Change-Id: I8313e37dc50b923610f1d02375fe402e64939d79
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Patch for "19.04_STABLE" branch: https://reviews.mahara.org/11274

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/11274
Committed: https://git.mahara.org/mahara/mahara/commit/7aea462939c7dda13ccac2872d9b1ee9fd7a1360
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: 19.04_STABLE

commit 7aea462939c7dda13ccac2872d9b1ee9fd7a1360
Author: Robert Lyon <email address hidden>
Date: Thu Jul 30 15:15:35 2020 +1200

Bug 1889340: Adding new index to 'tag' table

So that queries to the table go faster

Change-Id: I8313e37dc50b923610f1d02375fe402e64939d79
Signed-off-by: Robert Lyon <email address hidden>