From: David Johnston <polobo@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Problem with slow query with WHERE conditions with OR clause on primary keys
Date: Tue, 10 Dec 2013 23:18:38 -0800 (PST)
Message-ID: <1386746318622-5782822.post@n5.nabble.com> (raw)
In-Reply-To: <52A7A428.8070104@gmail.com>
References: <52A7A428.8070104@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Krzysztof Olszewski wrote
> Hi,
>
> my sql is very simple,
> returns one row,
> where conditions are assigned to primary keys
>
>
> */select g.gd_index, gd.full_name/**/
> /**/from gd g join gd_data gd on (g.id_gd = gd.id_gd)/**/
> /**/where gd.id_gd_data = 1111 OR g.id_gd = 1111;/*
>
>
> but generates "crazy" plan with Merge Join on big amount of rows (both
> tables contains 500000 rows)
> because Index scans ignore conditions, conditions are processed after
> index sacans on Merge Join
>
> */Merge Join (cost=0.00..46399.80 rows=2 width=115) (actual
> time=3.881..644.409 rows=1 loops=1)/**/
> /**/ Merge Cond: (g.id_gd = gd.id_gd)/**/
> /**/ Join Filter: ((gd.id_gd_data = 1111) OR (g.id_gd = 1111))/**/
> /**/ -> Index Scan using pk_gd on gd g (cost=0.00..14117.79
> rows=500001 width=40) (actual time=0.019..146.521 rows=500001 loops=1)/**/
> /**/ -> Index Scan using fki_gd on gd_data gd (cost=0.00..22282.04
> rows=500001 width=99) (actual time=0.016..157.384 rows=500001 loops=1)/**/
> /**/Total runtime: 644.460 ms/*
>
>
> model is very simple
>
>
> /CREATE TABLE gd (//
> // id_gd bigint NOT NULL,//
> // gd_index character varying(60) NOT NULL,//
> // notes text,//
> // notes_exists integer NOT NULL DEFAULT 0,//
> // CONSTRAINT pk_gd PRIMARY KEY (id_gd )//
> //)//
> //
> //
> //CREATE TABLE gd_data (//
> // id_gd_data bigint NOT NULL,//
> // id_gd bigint NOT NULL,//
> // short_name character varying(120) NOT NULL,//
> // full_name character varying(512) NOT NULL,//
> // notes text,//
> // notes_exists integer NOT NULL DEFAULT 0,//
> // CONSTRAINT pk_gd_data PRIMARY KEY (id_gd_data ),//
> // CONSTRAINT fk_gd FOREIGN KEY (id_gd)//
> // REFERENCES gd (id_gd) MATCH SIMPLE//
> // ON UPDATE NO ACTION ON DELETE NO ACTION//
> //)//
> //
> //CREATE INDEX fki_gd//
> // ON gd_data//
> // USING btree//
> // (id_gd );//
> /
>
>
> my configuration from (select * from pg_settings):
>
> "server_version";"9.1.10"
>
> Thank you for your help.
>
>
> Kris Olszewski
It cannot do any better since it cannot pre-filter either table using the
where condition without risking removing rows that would meet the other
table's condition post-join.
The query you are executing makes no sense to me: I don't understand why you
would ever filter on gd.id_gd_data given the model you are showing.
I believe your understanding of your model - or the model itself - is flawed
but as you have only provided code it is impossible to pinpoint where
exactly the disconnect resides. You can either fix the model or the query -
the later by implementing sub-selects with where clauses manually - which
then encodes an assumption about your data that the current query cannot
make.
Your model implies that a single gd record can have multiple gd_data records
associated with it.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-slow-query-with-WHERE-conditions-with-OR-clause...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: Problem with slow query with WHERE conditions with OR clause on primary keys
In-Reply-To: <1386746318622-5782822.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox