I'm trying to select a table in which I a have 3 or 4 columns that I want to match against an array/list with 1,000 or 10,000 (passed as parameter using a function) but it takes too much time, I readed that using indexes might help but still there ir poor performance during Query.

Here is my query:

SELECT 
"id", "parcel_number", "alternate_parcel_number", "parcel_tax_number"
FROM "addresses"
WHERE 
    "parcel_number" IN ('A080100', ... 'A0368895224')
    OR "alternate_parcel_number" IN ('A080100', ... 'A0368895224')
    OR "parcel_tax_number" IN ('A080100', ... 'A0368895224');

With an array/list of 1000 lines it takes about 2 min to return results.

Thanks.

CodePudding user response：

I found a solution on a website and change to VALUES gives a huge speed improvement.

URL: https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/

SELECT 
"id", "parcel_number", "alternate_parcel_number", "parcel_tax_number"
FROM "addresses"
WHERE 
    "parcel_number" = ANY (VALUES ('A080100'), ... ('A0368895224'))
    OR "alternate_parcel_number" = ANY (VALUES ('A080100'), ... ('A0368895224'))
    OR "parcel_tax_number" = ANY (VALUES ('A080100'), ... ('A0368895224'));

CodePudding user response：

It's not advisable to pass 10.000 values as parameters to a function. When I need to do that, my solution was:

1. Insert all parameters on a temporary table and associate all of them to a key value:

   CREATE TEMP_TABLE (PARAM_KEY INT, PARCEL_NO INT);

2. Pass only the key value to the function

3. Run the query:

   SELECT id, parcel_number, alternate_parcel_number, parcel_tax_number FROM "addresses" join temp_table on parcel_number = temp_param WHERE PARAM_key = :param_key

   UNION

   SELECT id, parcel_number, alternate_parcel_number, parcel_tax_number FROM "addresses" join temp_table on alternate_parcel_number = temp_param WHERE PARAM_key = :param_key

   UNION

   SELECT id, parcel_number, alternate_parcel_number, parcel_tax_number FROM "addresses" join temp_table on parcel_tax_number = temp_param WHERE PARAM_key = :param_key;