Linking multiple rows and columns for a WHERE IN clause in PostgreSQL

Question

So, I want to prepare the query like this:

SELECT id FROM users WHERE (branch, cid) IN $1; 

And then bind to it the variable length of the rowset, for example (('a','b'),('c','d')) .

In other words, something like:

 pg_prepare($users, 'users_query', 'SELECT id FROM users WHERE (branch, cid) IN $1'); $result = pg_execute($users, 'users_query', array("(('a','b'),('c','d'))"); 

The reason I need to split the two is because I want to prepare it once, and then run it many times with minimal overhead.

Answer 1

The fact that you get a sequential scan with only two entries does not make sense. An index will never be faster than selective scanning for such a tiny set. I built a small sample table close to yours and populated it with millions of rows, and the following query style consistently creates nice plans and fast execution:

 prepare s4 as select id from users join (select * from (values ($1,$2),($3,$4)) as v(branch, cid)) as p using (branch, cid); explain analyze execute s4('b11','c11','b1234','c1234'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.00..16.65 rows=1 width=4) (actual time=0.199..0.234 rows=2 loops=1) -> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=64) (actual time=0.002..0.003 rows=2 loops=1) -> Index Scan using u_i on users (cost=0.00..8.30 rows=1 width=16) (actual time=0.111..0.112 rows=1 loops=2) Index Cond: ((users.branch = "*VALUES*".column1) AND (users.cid = "*VALUES*".column2)) Total runtime: 0.425 ms 

It seems your real problem is how to associate a dynamically defined number of value pairs with your sql. My PHP is terribly rusty, and reading online documents reminded me how much I hate it, but I think the following will do what you want by building the sql of the above form with the number of dynamic creators of the value pair based on the number of values ​​you want to link . I don't have a php runtime, so I didn’t even check if it was syntactically fixed, but you should be able to get this idea and work out some trivial errors in my example.

 $values = array( 'a', 'b', 'c', 'd', // etc... ); $value_placeholders = ""; $sep = ""; for ($i=1; $i <= $count($values); $i+=2) { $value_placeholders = $value_placeholders . sprintf("($%u,$%u),", $i, $i+1) . $sep $sep = ","; } $sql = 'select id from users ' . 'join (select * from (values ' . $value_placeholders . ') as v(branch, cid)) as p' . 'using (branch, cid)'; $result = pg_query_params($dbconn, $sql, $values); 

If you really just have to have one prepared statement (and for a guy who can't be bothered to actually try his queries against a real data set instead of two records, we completely avoid talking about premature optimization ), I think I have there is an answer for you:

 create index u_i2 on users ((branch||cid)); prepare sa as select id from users where branch||cid in (select unnest($1::text[])); explain analyze execute sa(ARRAY['b1c1','b1234c1234']); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=12.17..645.78 rows=50000 width=4) (actual time=0.169..0.188 rows=2 loops=1) -> HashAggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.018..0.019 rows=2 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.011 rows=2 loops=1) -> Bitmap Heap Scan on users (cost=12.14..638.25 rows=500 width=16) (actual time=0.082..0.082 rows=1 loops=2) Recheck Cond: ((users.branch || users.cid) = (unnest($1))) -> Bitmap Index Scan on u_i2 (cost=0.00..12.02 rows=500 width=0) (actual time=0.078..0.078 rows=1 loops=2) Index Cond: ((users.branch || users.cid) = (unnest($1))) Total runtime: 0.275 ms 

Note. I could not find access to indexed access to line pairs. But if you create a functional index to concatenate two fields, then put the associated array of such concatenations, you will get a good quick index scan using nest-loop.