Optimisation of ILIKE query on LEFT outer join relationships-CodePudding

Pretty new to postgres and seem to have an issue here and I am not sure which direction to go in.

I have a query that is causing some performance issues and I cannot work out how to optimize it.

The query is actually quite simple:

SELECT transactions.* FROM transactions
LEFT OUTER JOIN companies ON "companies"."id" = "transactions"."company_id"
WHERE companies.code ILIKE 'w7%'
ORDER BY transactions.id desc LIMIT 10

I want to find all transactions that are for a company that has 777 in the name. There are around 20million transactions in the database and around 200 companies.

The query currently times out even though we have a LIMIT of 10. I believe this is because the ILIKE is not using an index so it's horribly slow.

Would this be an instance where you may want to add the WHERE filter to the JOIN instead? I have tested this and it works lightning-quick IF a record is found. If no record is found it again times out.

Is there a type of Index we should be looking at which would improve the speed here?

CodePudding user response：

You can try an EXISTS condition as you don't need any columns from the companies table:

SELECT tr.* 
FROM transactions tr
WHERE exists (select *  
              from companies c 
              where c.id = tr.company_id 
                and c.code ILIKE 'w7%')
ORDER BY tr.id desc 
LIMIT 10

But in the end, the order by is probably the bottleneck here. If e.g. 10 million transactions are returned, then sorting those 10 million rows will take some time.

CodePudding user response：

An EXPLAIN of the query would be helpful. That said, I don't think the ILIKE is bottlenecking you here but rather a scan on the transactions table. Followed by a potentially large ORDER BY sorting operation

Although (I think) the system is already doing this automagically, let's split up the functionalities and force the order of operations manually by using an (indexed) temptable.

Something along the lines of this:

SELECT id
  INTO TEMPORARY TABLE matching_companies
  FROM companies 
 WHERE companies.code ILIKE 'w7%';
 
CREATE UNIQUE INDEX idx_matching_companies_id ON matching_companies (id);

SELECT t.* 
  FROM transactions t
  JOIN matching_companies c
    ON c."id" = t."company_id"
ORDER BY t.id desc LIMIT 10;

Assuming few companies match the 777-requirement, this brings to light that an index on the transactions table on company_id should come in handy here.

I wouldn't be too surprised if simply adding the index might already speed up your original query because IMHO the system will split the query in pretty much the same operations (minus the idx_matching_companies_id maybe)