1

I have a view and it's a left join between two tables (table_a has hundreds of millions of rows, table_b has ~2 millions). The query

SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1

is very slow on the view (return in 30~60 seconds), but runs fast on both table.

Both table are using id as primary key. They joins together on id as well. I have the same structure in mysql, and it runs well (return in ms).

Is there anything I should set in Postgres to make this work?

Here's the table schema:

create table table_a (
id int primary key,
name varchar(40)
);

create table table_b(
id int primary key,
age_group varchar(5)
);

create view the_view as
select a.id, a.name, b.age_group
from table_a as a
left join table_b as b using(id);

Any ideas where to look into? Thank you.

Here's the explain analyze on the slow query:

psql=# explain analyze
       SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1;
  Limit  (cost=3296039.61..3296039.62 rows=1 width=4) (actual time=265398.380..265398.381 rows=1 loops=1)
   ->  Aggregate  (cost=3296039.61..3296039.62 rows=1 width=4) (actual time=265398.374..265398.374 rows=1 loops=1)
    ->  Index Only Scan using table_a_pkey on table_a a  (cost=0.57..2763865.85 rows=106434752 width=4) (actual time=0.158..133179.832 rows=106434713 loops=1)
        Heap Fetches: 0
Total runtime: 265398.506 ms
9
  • 1
    What version are you running? I recall reading somewhere that postgres isn't able to use indexes on views.. It may have been changed in a more recent version, though. Dec 8, 2014 at 19:00
  • I was using 9.3.5. I ran the explain on that query and it shows "Index Only Scan using a_pkey", so I'm guessing it's using the index?
    – Suanmeiguo
    Dec 8, 2014 at 19:40
  • 2
    @JoishiBodio That must've been a very long time ago. Views are passed through the rewrite engine recursively and treated much like the underlying view query had been written directly. Dec 8, 2014 at 19:40
  • @Suanmeiguo Please edit the question to show the full explain analyze result, or use explain.depesz.com Dec 8, 2014 at 19:41
  • @CraigRinger I just added the explain query. Thank you.
    – Suanmeiguo
    Dec 8, 2014 at 19:56

1 Answer 1

1

Bound to query

If you can only change the view, not the query: this is 100 % equivalent using a correlated subquery instead of the LEFT JOIN:

CREATE VIEW the_view_new AS
SELECT a.id, a.name
    , (SELECT age_group FROM table_b WHERE id = a.id) AS age_group
FROM   table_a a;

Your query as is just reads top and bottom row from the index now, IOW blazingly fast. This is a workaround, gets more complicated with more columns and may exhibit weak spots with other queries.

Better query

Your query:

SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1;
  • Only needs table_a. In the view, table_b is joined with a LEFT JOIN. Obviously, the query planner does realize that table_b is not needed for the result of min() and max() after a LEFT JOIN (contrary to what I assumed at first). The query plan does not mention table_b.

  • It can also only return a single row, so LIMIT 1 is only complicating matters for the query planner further, to no effect. (Seems not to be the tipping point here.)

  • The obfuscation confuses the planner enough to read the whole 100 million rows from the index (rows=106434752), while it would only need to look up first and last row. That's a whole lot of pointless work.

This is simpler, cleaner and faster, while returning the same:

SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM table_a;

As you can see in the EXPLAIN ANALYZE output of this SQL Fiddle with 100k rows in table_a:

Simple query

(actual time=0.010..0.010 rows=1 loops=1)

Your query:

(actual time=0.012..400.498 rows=100000 loops=1)

This looks like a weakness of the query planner in any case. We should repeat the test with pg 9.4 and possibly file a bug report ...

Bound to use the view

If you are bound to use the view (as commented), there is a workaround to convince the query planner (id must be NOT NULL - true in your case since PK):

SELECT (SELECT id AS min_id FROM the_view ORDER BY id ASC  LIMIT 1) AS min_id
     , (SELECT id AS max_id FROM the_view ORDER BY id DESC LIMIT 1) AS max_id;

Check the query plans in the fiddle: two times rows=1.

Aside: I suggest you take a look at the chapter Identifiers and Key Words in the manual.

5
  • Thank you for the link Erwin. I'll look into that. I tried explain analyze the query without "limit 1", still the same execution plan and takes the same time. You're absolutely right that there's better way to get the result using different query. Unfortunately that is out of my control.. Thanks again for your reply.
    – Suanmeiguo
    Dec 9, 2014 at 2:21
  • @Suanmeiguo: I added another solution that works with the view, too. Dec 9, 2014 at 2:40
  • Thank you for the other solution. I was seeking changes on the table or view schema, cause I don't have control on how the query runs or what query to use, but I have control to config the table or view. However, still an excellent answer, I would never think to get max min in this way. I might be able to suggest using this query instead of aggregation function. Is there any disadvantage of using this order by instead of aggregation function?
    – Suanmeiguo
    Dec 9, 2014 at 8:45
  • @Suanmeiguo: There is a corner case with NULL values. If id can be NULL, you have to use ORDER BY id DESC NULLS LAST to get the same result as with max(id), since max() ignores NULL values, but ORDER BY id DESC sorts them first (because they are last in default ASC sort order). Details here. This also may be (part of) the reason why the index scan derails in the first place ... Dec 9, 2014 at 10:54
  • @Suanmeiguo: OK, added one more solution. You might have put all your requirements into the question to begin with ... Dec 10, 2014 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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