Just make a simple join. Sub-queries does not provide the best result quite often
EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf
ON uf.level_id = l.id
WHERE l.user_id = 2;
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.
Best Answer
In general, if 2 queries are identical, they will have the same sql_id, and as a result, the same execution plan (which may change over the time, but it shouldn't depend on which user executes the query). (** It's oversimplified, because starting with 11g Oracle can generate multiple plans based on values of bind variables, presence of histograms ).
So first thing I'd check if they're really identical - run both of them, and check v$sql/v$sql_area .
Another thing is OPTIMIZER_MODE. When it's set to FIRST_ROWS(N), optimizer prefers NESTED LOOPS over HASH/MERGE JOINS ; when it's ALL_ROWS , HASH JOIN is more preferable for optimizer. It's quite common that different users set different optimizer mode , FIRST_ROWS is great for OLTP applications; ALL_ROWS is more suitable for DSS.
You can also check whether any outlines are used.
If you're on Oracle 11 and higher, check
IS_BIND_AWARE,IS_BIND_SENSITIVEvalues in v$sql.Finally, always check actual execution plan with
dbms_xplan.display_cursor, don't rely too much on the output ofEXPLAINwithout executing the query :- run query
- find sql_id in v$sql
- check plan
SELECT * FROM table(dbms_xplan.display_cursor('[sql_id from previous step'],null));