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 ANALYSE data in a few hours.
Here'e explain analyse results: http://explain.depesz.com/s/7e7
And here's server configuration:
version PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit
client_encoding UNICODE
effective_cache_size 4500MB
fsync on
lc_collate English_United States.1252
lc_ctype English_United States.1252
listen_addresses *
log_destination stderr
log_line_prefix %t
logging_collector on
max_connections 100
max_stack_depth 2MB
port 5432
search_path dbo, "$user", public
server_encoding UTF8
shared_buffers 1500MB
TimeZone Asia/Tbilisi
wal_buffers 16MB
work_mem 10MB
I'm running postgresql on a i5 cpu (4 core, 3.3 GHz), 8 GB of RAM and Crucial m4 SSD 128GB
UPDATE This looks like a bug in query planner. With the recomendation of Erwin Brandstetter I reported it to Postgresql bugs mailing list.
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.
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.