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
explain analyze
result, or use explain.depesz.com