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