title: PostgreSQL query taking too long
body: <p>I have database with few hundred millions of rows. I'm running the following query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <code>r."Name"</code> in where clause, it takes too much time to complete. I guess that PostgreSQL is doing a sequential scan on the <code>Payments</code> table (which contains the most rows), comparing each row one by one.</p>
  
  <p>Isn't postgresql smart enough to check first if <code>Roles</code> table contains any row with <code>Name</code> <code>'Moses'</code>? </p>
  
  <p>Roles table contains only 15 row, while Payments contains ~350 million. </p>
  
  <p>I'm running PostgreSQL 9.2.1.</p>
  
  <p>BTW, this same query on the same schema/data takes 0.024ms to complete on MS SQL Server.</p>
  
  <p>I'll update the question and post EXPLAIN ANALYSE data in a few hours.</p>
  
  <hr>
  
  <p>Here'e explain analyse results: <a href="http://explain.depesz.com/s/7e7" rel="noreferrer">http://explain.depesz.com/s/7e7</a></p>
  
  <hr>
  
  <p>And here's server configuration:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm running postgresql on a i5 cpu (4 core, 3.3 GHz), 8 GB of RAM and Crucial m4 SSD 128GB</p>
  
  <hr>
  
  <p><strong>UPDATE</strong>
  This looks like a bug in query planner. With the recomendation of Erwin Brandstetter I reported it to <a href="http://archives.postgresql.org/pgsql-bugs/2012-11/msg00089.php" rel="noreferrer">Postgresql bugs mailing list</a>.</p>