![]() ![]() OFFSET 0 - <- this forces the subquery to be evaluated separate from the rest of the query JOIN inventory_membership im ON im.inventory_id = inv.id - <- this changedįinally, as it said in this question, you might force the first query to be executed, before getting the inventory name, using a CTE or subquery with OFFSET 0. JOIN inventory inv ON inv.id = i.inventory_id ![]() The second is to use the query you had, but see if changing i.inventory_id to inv.id in inventory_membership changes anything. WHERE i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%'īut that means this select statement is executed 57k times, once for each row. JOIN inventory_membership im ON im.inventory_id = i.inventory_id The first, which you already tried, is to fetch the inventory name in a scalar subquery: SELECT i.*, (select name from inventory where id = i.inventory_id) as inventoryName Now, without the data, there are several things you can do, and I cannot tell which will be faster. The merge join needed the inventory_id in a sorted list (which is exactly what an index is), meaning that it preferred not to use your GIN indexes, since it thought that would be less efficient. The 8 rows is why the query planner prefers a merge join instead of the hash join, which works better when both tables are large. You note that you are interested mostly in the inventory name, and that there are only 8 rows in the inventory table. Also, the execution time went through the roof. ![]() ![]() > Seq Scan on inventory_membership im (cost=0.00.1.97 rows=97 width=4) (actual time=0.005.0.028 rows=87 loops=1)Īnd you can see the GIN indexes are gone and the only index the explain is using is the item_inventory_id_idx - which is the regular FK BTREE index. Where i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%' or brand Join inventory inv on inv.id = i.inventory_id Explain: explain analyze select i.* from item i However, when I join another table ( inventory table which only has id and name columns), the indexes disappear. We can see the item_name_idx, item_unique_attr_idx, item_category_idx and item_brand_idx GIN indexes are being used to index the conditions. Index Cond: (unique_attr ~~* '%blu%'::text) Hash Cond: (i.inventory_id = im.inventory_id) Where i.name ilike '%blu%' or unique_attr ilike '%blu%' or category ilike '%blu%' Join inventory_membership im on im.inventory_id = i.inventory_id Output of my explain: explain analyze select i.* from item i I have a table item with multiple text fields, like name, unique_attr, category, etc, and all of them I've indexed using the GIN (gin_trgm_ops) index for faster ilike queries, and indeed, even with a join to a table inventory_membership the indexes are used and speed up the execution time. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |