PostgreSQL query taking too long

Question

I have database with few hundred millions of rows. I'm running the following query: ``` select * from "Payments" as p inner join "PaymentOrders" as po on po."Id" = p."PaymentOrderId" inner join "Users" as u On u."Id" = po."UserId" INNER JOIN "Roles" as r on u."RoleId" = r."Id" Where r."Name" = 'Moses' LIMIT 1000 ``` When the where clause finds a match in database, I get the result in several milliseconds, but if I modify the query and specify a non-existent `r."Name"` in where clause, it takes too much time to complete. I guess that PostgreSQL is doing a sequential scan on the `Payments` table (which contains the most rows), comparing each row one by one. Isn't postgresql smart enough to check first if `Roles` table contains any row with `Name` `'Moses'`? Roles table contains only 15 row, while Payments contains ~350 million. I'm running PostgreSQL 9.2.1. BTW, this same query on the same schema/data takes 0.024ms to complete on MS SQL Server. I'll update the question and post EXPLAIN…

Accepted Answer

Finally successful attempt

My other idea - as per comment: What happens if you remove the `LIMIT` clause for the case where no role is found? I have a suspicion that it will result in the fast plan - making `LIMIT` the culprit here.

You may be able to solve your problem by pushing down your query into a subquery and applying the `LIMIT` only to the outer query (untested):

```
SELECT *
FROM  (
   SELECT *
   FROM   "Roles"         AS r  
   JOIN   "Users"         AS u  ON u."RoleId" = r."Id"
   JOIN   "PaymentOrders" AS po ON po."UserId" = u."Id"
   JOIN   "Payments"      AS p  ON p."PaymentOrderId" = po."Id"
   WHERE  r."Name" = 'Moses'
  ) x
LIMIT  1000;
```

As per comment: @Davita tested and ruled out this workaround. @Kevin's answer later clarified why the workaround failed: use a CTE instead of the subquery. Or check for existence of a role, before you employ the big query to eliminate the bad case.

This leaves questions for PostgreSQL concerning the optimization of queries with `LIMIT`.

There have been a number of recent bug reports concerning query plans with `LIMIT`. I quote Simon Riggs commenting on one of these reports here:

Very bad plans with LIMIT are frequent. This is bad for us because adding LIMIT usually/is supposed to make queries faster, not slower.

We need to do something.

First attempt with no success

I missed that @Craig already mentioned `join_collapse_limit` in the comments. So that was of limited use:

Does reordering the `JOIN` clauses have any effect?

```
SELECT *
FROM   "Roles"         AS r  
JOIN   "Users"         AS u  ON u."RoleId" = r."Id"
JOIN   "PaymentOrders" AS po ON po."UserId" = u."Id"
JOIN   "Payments"      AS p  ON p."PaymentOrderId" = po."Id"
WHERE  r."Name" = 'Moses'
LIMIT  1000
```

Related: you did not by chance mess with the setting of `join_collapse_limit` or `geqo_threshold`? Very low setting might prevent the planner from reordering your `JOIN` clauses, which might explain your problem.

If that does not solve the case, I would try to create an index on `"Roles"(Name)`. Not that this makes any sense with only 15 rows, but I would try to eliminate the suspicion that invalid statistics or cost parameters (or even a bug) make the planner believe the sequential scan on "Roles" to be more expensive than it is.

PostgreSQL query taking too long

Solution

Problem