Best way to select specific rows using 1 query in PostgreSQL?

user_16517709 0
Viewed 241

I have an application in Java requiring me to find specific records given specific conditionals. For example, I have the table:

id song artist record_label
1 Never Gonna Give You Up Rick Astley Rickroll'd Records
2 Blackbird The Beatles Apple Records
3 Yesterday The Beatles Apple Records
4 WonderWall Oasis Columbia Records

I'd like to bulk query a subset of them based on specific conditions. Something similar to:

SELECT id FROM songs 
WHERE 
  (song = 'Blackbird' AND artist = 'The Beatles' AND record_label = 'Apple Records') OR 
  (song = 'WonderWall' AND artist = 'Oasis' AND record_label = 'Columbia Records') OR 
  (song = 'Yesterday' AND artist = 'The Beatles' AND record_label = 'Apple Records')

The application is going to receive these conditions from the user and could be trying to find thousands of these records. As a result, I'm hoping to find a way to do this without any case of SQL injection and in as little queries as possible.

MY first approach would be some flavor of PreparedStatement where I iterate through this SQL query to query each individual record:

SELECT id from songs WHERE song = ? AND artist = ? AND record_label = ?

This prevents SQL injection, but I feel like this could be optimized more as we hammer the DB with thousands of these requests in seconds.

Another option is to create a temp table, import our passed conditions into the temp table and do an INNER JOIN on the songs table to only retrieve the rows that match between the 2. This solves both problems, but it requires a good amount of development work.

I'm wondering if there's any other methods I haven't taken into account. Thanks in advance for any suggestions!

postgresql java
2 Answers

One way I can think of, is to pass the parameters as a JSON string, then you can have a single parameter:

SELECT id 
FROM songs 
WHERE (song, artist, record_label) 
        in (select item ->> 'song', 
                   item ->> 'artist', 
                   item ->> 'record_label'
            from jsonb_array_elements(cast(? as jsonb)) as p(item)
            );

The parameter would then be a String passed through PreparedStatement.setString().

For your sample query e.g.

[
   {"song": "Blackbird", "artist": "The Beatles", "record_label": "Apple Records"},
   {"song": "Wonderwall", "artist": "Oasis", "record_label": "Columbia Records"},
   {"song": "Yesterday", "artist": "The Beatles", "record_label": "Apple Records"}
]

Not sure about performance, but the OR condition is usually a performance killer to begin with, so the small overhead of parsing and unnesting the JSON array shouldn't make a big difference.

user_3303150

A simple test show how Spring Boot does not batch the query:

myRepo.findAllById(IntStream
    .range(0, 1000000)
    .mapToObj(i -> UUID.randomUUID())
    .collect(toList()));

producing a big query

... where myRepo0_.id in (? , ? , ?, ...

and failing with big ranges.

The default saveAll JPA implementation neither use batching.

Probably you should test the speed of perform one by one queries:

List<MyResult> myResults = myQueryParams
    .stream()
    .map(qp -> myRepo.findByMyParams(qp...))
    .collect(toList());

If is too slow, check the query is optimal and only if the speed is really important (e.g. if the transaction is too long for a HTTP request you can do it asynchronously, paging, ...) then use batching.

To batching queries you can create a temporary (only for your specific query, it will not lock db objects):

long t0 = System.currentTimeMillis();
try(Connection cnx = primaryDataSource.getConnection()) {
    cnx.setAutoCommit(false);

    cnx.createStatement().execute("create temp table resultSet(id uuid)");

    PreparedStatement s = cnx.prepareStatement("insert into resultSet(id) select id from tq_event where id = ?");
    for(int i = 0; i < 1000000; i++) {
        s.setObject(1, UUID.fromString("39907bfb-f77a-47a3-9ab6-2b4794c7d6ec"));
        s.addBatch();
    }
    s.executeBatch();
    cnx.commit();

    ResultSet rs = cnx.createStatement().executeQuery("select id from resultSet");
    while(rs.next())
        System.out.printf("%s%n", rs.getString(1));

    cnx.createStatement().execute("drop table resultSet");
    cnx.commit();
}
System.out.printf("Time: %d mS%n", System.currentTimeMillis() - t0);

running from my PC querying via ssh to a cloud PostgreSQL database (very slow connection), query 1.000.000 rows take Time: 179640 mS (avg 0.18mS per row).

You can try the @a_horse_with_no_name solution:

PreparedStatement s = cnx.prepareStatement("select uid from test where (id) in (select (k ->> 'id')::integer from jsonb_array_elements(cast(? as jsonb)) as p(k))");
s.setString(1, "[" + IntStream.range(1, 1000000).mapToObj(i -> "{\"id\": " + i + "}").collect(joining(",")) + "]");
ResultSet rs = s.executeQuery();
while(rs.next())
    System.out.printf("%s%n", rs.getString(1));

witch run much more faster (only one query is sent to the server) but casting datatypes could be a problem and you will have a limit of 2Gbytes! for all your parameters (looks good for many cases).

user_15407490
Related