Re: Towards faster keyword searches

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--SL2H6irt455o2Q8LJHhhhFMTAIhc2xHdT
From: Jeff Fearn <[email protected]>
To: [email protected]
Message-ID: <fa99a66e-293e-9c13-2d47-3d7e3f8b36d4-H+wXaHxf7aLQT0dZR+AlfA@public.gmane.org>
Subject: Re: Towards faster keyword searches
References: <D821D042-2A15-424B-94EF-15A4121764C4-4eJtQOnFJqFBDgjK7y7TUQ@public.gmane.org>
In-Reply-To: <D821D042-2A15-424B-94EF-15A4121764C4-4eJtQOnFJqFBDgjK7y7TUQ@public.gmane.org>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

On 26/09/2016 12:46, Dylan Hardison wrote:
> When you have a keyword on 50,000+ bugs it means you're building a sql qu=
ery with a 50,000 list in the form of IN (50,000 ids).
> This is gets close to the limits mysql has on queries, at least and it's =
pretty slow.

Not only that, when you get to 65K you hit parameter limits in DBD::Pg :(

I've just had to fix a bug for this related to viewing bugs in a group.
I solved it by replacing the use of new_from_list with new_from_where
which takes the SQL used to generate the bug id list and uses it in the
query.

https://jfearn.fedorapeople.org/65KBugsInAGroup.patch

I'm not very happy with it because it requires you knowing exactly how
the sql will be used and coupling is bad, mmmk.

A more general approach to doing sub-selects instead of ID lists would
be great.

> There are a whole class of these -- anything that is a 'multiselect' type=
 search. I believe someone suggested embedding these queries (at least as a=
n option)
> as a sub select and in general I think that's a good idea. An even nicer =
idea is to just let elastic search do the searching.
> One of these approaches I hope to explore with an outreachy intern.

The effect of using ID lists instead of sub selects is wider than just
searching, as the above patch demonstrates.

> However, for the moment I really need keyword searches to be fast, so I s=
tarted looking at the search code[1].
> I started at the problem backwards -- first by looking at where we build =
search objects (the tree-like Bugzilla::Search::Clause::* stuff).
> For searches with a single keyword search, I thought it would be nice to =
turn that into a join.
>=20
> Then I wrote that, and it appears the code I wrote actually works general=
ly for multiple keywords (although you then start wondering what the max nu=
mber of joins is.)
>=20
> Anyway, it's not finished code but it does work on a test install.
>=20
> It's in a github branch: https://github.com/dylanwh/bugzilla/tree/fast-ke=
ywords,
> you can look at the diff here: https://github.com/dylanwh/bugzilla/commit=
/e88bdf7168c6723d9930bc771ea93c93c3dec6b0
>=20
> I will be polishing this up for a review, but I wanted to have other peop=
le look at it first.
>=20
> Here's an example of a query it builds, for single_keyword=3Dbatman AND s=
ingle_keyword:frog
>=20
> SELECT bugs.bug_id AS bug_id, bugs.priority AS priority, bugs.bug_severit=
y AS bug_severity
>   FROM bugs
> LEFT JOIN bug_group_map AS security_map ON bugs.bug_id =3D security_map.b=
ug_id
> LEFT JOIN cc AS security_cc ON bugs.bug_id =3D security_cc.bug_id AND sec=
urity_cc.who =3D 1
> INNER JOIN priority AS map_priority ON bugs.priority =3D map_priority.val=
ue
> INNER JOIN bug_severity AS map_bug_severity ON bugs.bug_severity =3D map_=
bug_severity.value
> LEFT JOIN keywords AS keywords_1 ON bugs.bug_id =3D keywords_1.bug_id
> LEFT JOIN keyworddefs AS keyworddefs_1 ON keywords_1.keywordid =3D keywor=
ddefs_1.id
> LEFT JOIN keywords AS keywords_2 ON bugs.bug_id =3D keywords_2.bug_id
> LEFT JOIN keyworddefs AS keyworddefs_2 ON keywords_2.keywordid =3D keywor=
ddefs_2.id
>  WHERE bugs.creation_ts IS NOT NULL
>    AND ( (security_map.group_id IS NULL OR security_map.group_id IN (1,10=
,11,14,12,13,9,4,8,5,6,7,3,2))
>         OR (bugs.reporter_accessible =3D 1 AND bugs.reporter =3D 1)
>         OR (bugs.cclist_accessible =3D 1 AND security_cc.who IS NOT NULL)
>         OR bugs.assigned_to =3D 1
> )
>    AND  bugs.resolution IN ('')  AND keyworddefs_1.name =3D 'batman' AND =
INSTR(keyworddefs_2.name, 'frog') =3D 0
> GROUP BY bugs.bug_id
> ORDER BY map_priority.sortkey, map_priority.value, map_bug_severity.sortk=
ey, map_bug_severity.value
> LIMIT 500-
> To view or change your list settings, click here:
> <https://lists.bugzilla.org/cgi-bin/[email protected]>
>=20

I ran this on a copy of our DB and it seems to perform well. I did have
to chop off the order by as it's not leagl in Pg to have a group by
clause and then order by things not in the group by clause or an
aggregate function.

Cheers, Jeff.




--SL2H6irt455o2Q8LJHhhhFMTAIhc2xHdT
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAEBCAAGBQJX6JtKAAoJELs3R4zxGZvK6nEH/jiy32muaXj0Xmexy48WjOBY
CoDaMNFKQph4XGTnScmO9m/mzjIZZCybkkTwCrcV9ySHrFSDhHAs/WpzUu8eXzlM
9ibqX3vfmLCkAJyEqdkzAU1J2hxh/UItqKeGUjy63D2JDqbwmBM545Ouh3SGTX+T
+Ykmeo/IOzQebK52lSpv9pJmlEP9l+MdGlyOJD6bF6yLCr6GQd/zc8drXBB4Bwtm
ZYoftpwjcTSGMXuwR7JOVU1wUiQ4LXbuMCzmngGtN4vhOpPDDKQF9LY/MiV7T1Qt
XVjqrjloCkFHrZoVLKHHur3wPm0nD6VjBAd7b21Op7bjgb38B9iPm87pyxrAM4c=
=wDUp
-----END PGP SIGNATURE-----

--SL2H6irt455o2Q8LJHhhhFMTAIhc2xHdT--