PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL CTE by id string is very cheap, but by id from query is very expensive

I use the id directly in the query, it has a low cost:

-- Query 1.
-- Nested Loop Semi Join  (cost=2.27..280.78 rows=1 width=129)
EXPLAIN SELECT *
FROM view_companies_extra
WHERE company_id iN (
  SELECT id
  FROM companies
  WHERE id = 'cddba3ad-dd13-48bb-a5b0-f0e325f27d51'
);

And if I use the id from query, I get a much higher cost?

-- Query 2.
-- Seq Scan on companies  (cost=0.00..18581.05 rows=1 width=16)
--  Filter: ((number)::text = '7731394650'::text)
EXPLAIN SELECT id
FROM companies
WHERE number = '7731394650';

-- Query 3.
-- Nested Loop  (cost=3.10..10324011.11 rows=12 width=129)
EXPLAIN SELECT *
FROM view_companies_extra
WHERE company_id IN (
  SELECT id
  FROM companies
  WHERE number = '7731394650'
);

If you run "query 2" to get the id, and then use it in query "query 1", it should be very fast.

But when I combine all this in one "query 3", it takes an extremely long time. Why is that?

Postgres 13.10 and 13.14.

Tags:

2

Answers


  1. 0

    In query 1 you will try to select company using id which have there primary index but in query 3 you try to find by another field may be there is no index defined over it so you can define index against this field then use query 3.

    Login or Signup to reply.
  2. 0

    I think, in query3 first full view view_companies_extra composed (all joins) and then filter WHERE number = '7731394650' is applied.

    Column number is not selective in your view, possible, not have suitable index.

    ( SELECT id
  FROM companies
  WHERE id = 'cddba3ad-dd13-48bb-a5b0-f0e325f27d51' )

    SELECT id
FROM companies
WHERE number = '7731394650'

    returns 1 row and

    ( SELECT id
  FROM companies
  WHERE number = '7731394650')

    or query3 returns 12 rows – check view, problem may be inside view.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search