8

I have a relatively simple query on a table with 1.5M rows:

SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;

EXPLAIN ANALYZE output:

Limit  (cost=8.84..12.86 rows=1 width=8) (actual time=0.985..0.986 rows=1 loops=1)
  ->  Bitmap Heap Scan on publication  (cost=8.84..12.86 rows=1 width=8) (actual time=0.984..0.985 rows=1 loops=1)
        Recheck Cond: ((mtid = 9762715) OR (last_modifier = 21321))
        ->  BitmapOr  (cost=8.84..8.84 rows=1 width=0) (actual time=0.971..0.971 rows=0 loops=1)
              ->  Bitmap Index Scan on publication_pkey  (cost=0.00..4.42 rows=1 width=0) (actual time=0.295..0.295 rows=1 loops=1)
                    Index Cond: (mtid = 9762715)
              ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..4.42 rows=1 width=0) (actual time=0.674..0.674 rows=0 loops=1)
                    Index Cond: (last_modifier = 21321)
Total runtime: 1.027 ms

So far so good, fast and uses the available indexes.
Now, if I modify a query just a bit, the result will be:

SELECT mtid FROM publication
WHERE mtid IN (SELECT 9762715) OR last_modifier=21321
LIMIT 5000;

The EXPLAIN ANALYZE output is:

Limit  (cost=0.01..2347.74 rows=5000 width=8) (actual time=2735.891..2841.398 rows=1 loops=1)
  ->  Seq Scan on publication  (cost=0.01..349652.84 rows=744661 width=8) (actual time=2735.888..2841.393 rows=1 loops=1)
        Filter: ((hashed SubPlan 1) OR (last_modifier = 21321))
        SubPlan 1
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Total runtime: 2841.442 ms

Not so fast, and using seq scan...

Of course, the original query run by the application is a bit more complex, and even slower, and of course the hibernate-generated original is not (SELECT 9762715), but the slowness is there even for that (SELECT 9762715)! The query is generated by hibernate, so it is quite a challenge to change them, and some features are not available (e.g. UNION is not available, which would be fast).

The questions

  1. Why cannot the index be used in the second case? How could they be used?
  2. Can I improve query performance some other way?

Additional thoughts

It seems that we could use the first case by manually doing a SELECT, and then putting the resulting list into the query. Even with 5000 numbers in the IN() list it is four times faster than the second solution. However, it just seems WRONG (also, it could be 100 times faster :) ). It is completely incomprehensible why the query planner uses a completely different method for these two queries, so I would like to find a nicer solution to this problem.

  • Can you somehow rewrite your code so that hibernate generates a JOIN instead of the IN ()? Also, has publication been analyzed recently? – dezso Sep 7 '15 at 12:05
  • Yes, I did both VACUUM ANALYZE and VACUUM FULL. There was no change in performance. As to the second, AFAIR we tried that and it did not affect query performance significantly. – P.Péter Sep 7 '15 at 12:41
  • 1
    If Hibernate fails to generate a proper query, why don't you just use raw SQL? That's like insisting on Google translate while you already know better how to express it in English. As to your question: it really depends on the actual query hidden behind (SELECT 9762715). – Erwin Brandstetter Sep 7 '15 at 13:16
  • As I mentioned below, it is slow even if the inner query is (SELECT 9762715). To the hibernate question: it could be done, but requires serious code rewrite, as we have user-defined hibernate criteriaqueries that are translated on-the-fly. So essentially we would be modifying hibernate which is a huge undertaking with a lot of possible side effects. – P.Péter Sep 7 '15 at 15:49
6

My colleague has found a way to change the query so that it needs a simple rewrite and does what it needs to do, i.e. doing the subselect in one step, and then doing the further operations on the result:

SELECT mtid FROM publication 
WHERE 
  mtid = ANY( (SELECT ARRAY(SELECT 9762715))::bigint[] )
  OR last_modifier=21321
LIMIT 5000;

The explain analyze now is:

 Limit  (cost=92.58..9442.38 rows=2478 width=8) (actual time=0.071..0.074 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.01..0.02 rows=1 width=0) (actual time=0.010..0.011 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Bitmap Heap Scan on publication  (cost=92.56..9442.36 rows=2478 width=8) (actual time=0.069..0.070 rows=1 loops=1)
         Recheck Cond: ((mtid = ANY (($1)::bigint[])) OR (last_modifier = 21321))
         Heap Blocks: exact=1
         ->  BitmapOr  (cost=92.56..92.56 rows=2478 width=0) (actual time=0.060..0.060 rows=0 loops=1)
               ->  Bitmap Index Scan on publication_pkey  (cost=0.00..44.38 rows=10 width=0) (actual time=0.046..0.046 rows=1 loops=1)
                     Index Cond: (mtid = ANY (($1)::bigint[]))
               ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..46.94 rows=2468 width=0) (actual time=0.011..0.011 rows=0 loops=1)
                     Index Cond: (last_modifier = 21321)
 Planning time: 0.704 ms
 Execution time: 0.153 ms

It seems we can create a simple parser that finds and rewrites all subselects this way, and add it to a hibernate hook to manipulate the native query.

  • That sounds fun. Isn't it easier to just remove all the SELECTs, like you have in your first query in the question? – dezso Sep 14 '15 at 11:10
  • Of course, I could do a two-step approach: do a SELECT separately, and then do the outer select with a static list after the IN. However, that is significantly slower (5-10 times if the the subquery has more than a few results), as you have extra network round-trips plus you have postgres format a lots of results and then java parsing those results (and then doing the same again backwards). The solution above does the same semantically, while leaving the process inside postgres. All in all, currently this seems to be the fastest way with the smallest modification in our case. – P.Péter Sep 14 '15 at 11:30
  • Ah, I see. What I didn't know is that you can get many IDs at a time. – dezso Sep 14 '15 at 11:33
5

The core of the problem becomes obvious here:

Seq Scan on publication (cost=0.01..349652.84 rows=744661 width=8) (actual time=2735.888..2841.393 rows=1 loops=1)

Postgres estimates to return 744661 rows while, in fact, it turns out to be a single row. If Postgres does not know better what to expect from the query it cannot plan better. We would need to see the actual query hidden behind (SELECT 9762715) - and probably also know table definition, constraints, cardinalities and data distribution. Obviously, Postgres is not able to predict how few rows will be returned by it. There may be ways to rewrite the query, depending on what it is.

If you know that the subquery can never return more than n rows, you can just tell Postgres by using:

SELECT mtid
FROM   publication
WHERE  mtid IN (SELECT ... LIMIT n) --  OR last_modifier=21321
LIMIT  5000;

If n is small enough, Postgres will switch to (bitmap) index scans. However, that only works for the simple case. Stops working when adding an OR condition: the query planner can't currently cope with that.

I rarely use IN (SELECT ...) to begin with. Typically there is a better way to implement the same, often with an EXISTS semi-join. Sometimes with a (LEFT) JOIN (LATERAL) ...

The obvious workaround would be to use UNION, but you ruled that out. I can't say more without knowing the actual subquery and other relevant details.

  • 2
    There is no query hidden behind (SELECT 9762715)! If I run that exact query that you see above. Of course, the original hibernate query is a bit more complicated, but I (think I) managed to pinpoint where the query planner goes astray, so I presented that part of the query. However, the above explains and queries are verbatim ctrl-c-v. – P.Péter Sep 7 '15 at 15:35
  • As to the second part, the inner limit does not work: EXPLAIN ANALYZE SELECT mtid FROM publication WHERE mtid IN (SELECT 9762715 LIMIT 1) OR last_modifier=21321 LIMIT 5000; also does a sequential scan and also runs for about 3 seconds... – P.Péter Sep 7 '15 at 15:43
  • @P.Péter: It works for me in my local test with an actual subquery on Postgres 9.4. If what you show is your real query, then you have your solution already: Use the first query in your question with a constant instead of a subquery. – Erwin Brandstetter Sep 7 '15 at 15:52
  • Well, I also tried a subquery on a new test table: CREATE TABLE test (mtid bigint NOT NULL, last_modifier bigint, CONSTRAINT test_property_pkey PRIMARY KEY (mtid)); CREATE INDEX test_last_modifier_btree ON test USING btree (last_modifier); INSERT INTO test (mtid, last_modifier) SELECT mtid, last_modifier FROM publication;. And the effect was still there for the same queries on test: any subquery resulted on a seq scan... I tried both 9.1 and 9.4. The effect is the same. – P.Péter Sep 7 '15 at 16:07
  • 1
    @P.Péter: I ran the test again and realized I had tested without the OR condition. The trick with LIMIT only works for the simpler case. – Erwin Brandstetter Sep 8 '15 at 2:43
1

Answer to a second question: Yes, you can add ORDER BY to your subquery, which will have positive impact. But it's simillar to "EXISTS (subquery)" solution in performance. There is a significant difference even with subquery resulting in two rows.

SELECT mtid FROM publication
WHERE mtid IN (SELECT #column# ORDER BY #column#) OR last_modifier=21321
LIMIT 5000;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.