List columns of multiple row as rows (postgres SQL)

Question

I tried to think of a sql query to get results consisting of multiple columns from multiple records, which the table is like:

key_1     key_2
--------------------
1         2
1         3
1         4
5         6

Both key_1 and key_2 are primary keys from another table. And the result I want to obtain is getting every key that are related to the key '1', which is:

key
--------
1
2
3
4

I tried the following query:

SELECT key_1 as key FROM tbl_keys_link WHERE key_1 IN (1)
UNION
SELEVY key_2 as key FROM tbl_keys_link WHERE key_2 IN (1)

But I examined it using pgadmin's 'Explain' function, seems the process time of this query is not very optimal. Is there any other ways to construct a query to achieve this with a better performance? Thanks!

Gordon Linoff · Accepted Answer · 2014-07-27 11:48:16Z

1

You can try phrasing the query like this:

select a.key
from anothertable a
where exists (select 1 from tbl_keys_link p pairs.key_1 = a.key and pairs.key_2 = 1) or
      exists (select 1 from tbl_keys_link p pairs.key_2 = a.key and pairs.key_1 = 1);

For performance, you will want two indexes: tbl_keys_link(key_1, key_2) and tbl_keys_link(key_2, key1).

This eliminates the duplicate elimination step (needed for the distinct). Also, the query should just scan the other table and do one or two index lookups to identify the matching keys.

edited Jul 27, 2014 at 11:48

answered Jul 25, 2014 at 13:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

peakingcube Over a year ago

I created the index pairs(key_1, key_2), but when I tried to use the SQL you suggested, it stated that that is error regarding the use of pairs p (Error msg: "pairs" is an index.) And one thing I am not sure about is that, according to the conditions in the WHERE clause, to execute the SELECT outside, both exist clause should contain at least 1 record, but while referring to the table above, the value '1' will only occur in the column key_1, so I think this query will return an empty result?

Gordon Linoff Over a year ago

@alanlo . . . pairs was just the name I was using for the table. And you are right. The connection between the two clauses should be or for your logic, not and.

user330315 · Accepted Answer · 2014-07-25 13:20:13Z

0

You can try this:

with keys as (
  select key_1, key_2
  from tbl_keys_link 
  where key_1 = 1 
     or key_2 = 1
)
SELECT distinct unnest(array[key_1, key_2]) 
from keys
order by 1;

But I have no idea if that is more efficient. At least it only needs to scan the table once. If you have an index on each column, Postgres might be able to use those indexes for searching the "relevant" rows.

answered Jul 25, 2014 at 13:20

user330315

Collectives™ on Stack Overflow

List columns of multiple row as rows (postgres SQL)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related