Tom,
Just to clarify, is the lefthand input customers or balances?
And turning off merge joins "fixes" everything, including the runtime:
production=> set enable_mergejoin = off;
SET
production=> explain analyze SELECT ac.* FROM balances ac JOIN customers o
ON (o.id = ac.customer_id AND o.group_
Sean Rhea writes:
> No, the customers table is not 100% the same. This is a live production
> system, so the data is (unfortunately) changing under us a bit here. That
> said, there are still some strange things going on. I just reran
> everything. The query plan time hasn't changed, but as Jeremy
All,
No, the customers table is not 100% the same. This is a live production
system, so the data is (unfortunately) changing under us a bit here. That
said, there are still some strange things going on. I just reran
everything. The query plan time hasn't changed, but as Jeremy, Igor, and
David all
On 10 October 2015 at 08:52, Sean Rhea wrote:
>
> 1. When I join two tables with "WHERE id IN (...)" versus with an explicit
> join, and the join column for the inner table is a primary key, I would
> expect
> the same behavior in both cases, but the optimizer is choosing a merge
> join in
> one
From: Sean Rhea [mailto:sean.c.r...@gmail.com]
Sent: Friday, October 09, 2015 4:30 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Merge join vs merge semi join against primary key
It does the merge (not-semi) join:
production=> explain analyze SELECT ac.* F
Hello,
I'm seeing some inexplicable (to me) behavior in PostgreSQL 9.2. I checked
the archives, but I still can't explain it. Apologies if I missed something.
1. When I join two tables with "WHERE id IN (...)" versus with an explicit
join, and the join column for the inner table is a primary key,
On 09/10/15 20:52, Sean Rhea wrote:
[...]
>-> Index Scan using customers_pkey on customers (cost=0.00..64192.97
> rows=184 width=8) (actual time=103.354..126.459 rows=359 loops=1)
> Filter: (group_id = 45)
> Rows Removed by Filter: 141684
> Total runtime: 146.659 ms
[...]
>
It does the merge (not-semi) join:
production=> explain analyze SELECT ac.* FROM balances ac JOIN customers o
ON (o.id = ac.customer_id AND o.group_id = 45);
QUERY PLAN
--
Hello,
I'm seeing some inexplicable (to me) behavior in PostgreSQL 9.2. I checked
the archives, but I still can't explain it. Apologies if I missed something.
1. When I join two tables with "WHERE id IN (...)" versus with an explicit
join, and the join column for the inner table is a primary key,