PostgreSQL strange query plan for my query - Mailing list pgsql-performance

From David Popiashvili
Subject PostgreSQL strange query plan for my query
Date
Msg-id BLU002-W714109E032B038410CA3C7D1510@phx.gbl
Whole thread Raw
Responses Re: PostgreSQL strange query plan for my query  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
Re: PostgreSQL strange query plan for my query  (Craig James <cjames@emolecules.com>)
List pgsql-performance
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.


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


Original question source http://stackoverflow.com/questions/13407555/postgresql-query-taking-too-long#comment18330095_13407555


Thank you very much.

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: performance regression with 9.2
Next
From: "Albe Laurenz"
Date:
Subject: Re: PostgreSQL strange query plan for my query