Question 217308 in PostgreSQL Question posted in
The official documentation can be found here.

Optimal query to PostgreSQL and complex index on 3 columns, when 2 columns have static values and 3-rd one uses operator IN

There’s a table with compound index (col1, col2, col3). There’s a lot of data in this table.
Want to build query for example WHERE col1 = 2 AND col2 = 12 AND col3 IN (1, 2, 3, …, 40)
Is there a way to use index fully (with 3 columns)?

When I’m trying

SELECT *
FROM table t
WHERE t.col1 = 2
    AND t.col2 = 12
    AND t.col3 IN (1, 2, 3, ..., 40)

Postgres planner makes index scan on (col1, col2) and then uses SeqScan to filter one by one 400k of rows with col3 IN (1, 2, 3, ..., 40)

If I try

SELECT *
FROM table t
WHERE (col1, col2, col3) IN VALUES (2, 12, 1), (2, 12, 2), (2, 12, 3), ... ,(2, 12, 40)

it gives error:

temporary file size exceeds temp_file_limit

So it works slow. Is there a way to make postgres use somehow compound index for 3 columns?

Tags:

2

Answers


  1. 0

    You could try loading the col3 possible values into a bona fide table and then rewriting the query to the following:

    SELECT t1.*
FROM yourTable t1
WHERE t1.col1 = 2 AND
      t1.col2 = 12 AND
      EXISTS (
          SELECT 1
          FROM table2 t2
          WHERE t2.col3 = t1.col3
      );

    This assumes that table2 has the following structure:

    table2:
col3
1
2
3
...
40

    table1 might be able to use an index on (col1, col2, col3). An index should also be placed on table2 (col3), to ensure rapid lookups.

    Login or Signup to reply.
  2. 0

    Based on your comment, it looks like we can force use of the index through an explicit join on (col1, col2, col3) = (arg1, arg2, arg3).

    I don’t know how you are calling this query, but if called from a host language that allows passing an int[] type through the database driver, my query would look like this:

    with invars as (
  select 2 as c1val, 12 as c2val,
         array[1, 2, 3, 4, 5, 6, 40] as c3vals  
), search_tuples as (
  select i.c1val, i.c2val, u.c3val
    from invars i
         cross join lateral unnest(i.c3vals) as u(c3val)
)
select t.*
  from search_tuples s
       join table1 t 
    on (t.col1, t.col2, t.col3) = (s.c1val, s.c2val, s.c3val);

    A working fiddle with random test records and explain

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search