Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VqbPI-0000cU-G0 for pgsql-performance@arkaria.postgresql.org; Wed, 11 Dec 2013 04:28:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VqbPH-0001Zc-8T for pgsql-performance@arkaria.postgresql.org; Wed, 11 Dec 2013 04:28:19 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VqWlU-0002Hv-DO for pgsql-performance@postgresql.org; Tue, 10 Dec 2013 23:30:56 +0000 Received: from mail-ee0-x22d.google.com ([2a00:1450:4013:c00::22d]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VqWlQ-00020J-Mw for pgsql-performance@postgresql.org; Tue, 10 Dec 2013 23:30:55 +0000 Received: by mail-ee0-f45.google.com with SMTP id d49so2499444eek.18 for ; Tue, 10 Dec 2013 15:30:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject :content-type; bh=3MN9u3y4r3AGfUTbz07DNY5gkG7rfz0ZMtSy8RUf848=; b=ZneMtN+5F58X6BNrJAojIsk2y9jqscOs3cDw1CPNFpT14+A1MIbCtjbqhhsV5fXEW9 UBjE1vx3oZMLC1oaZ0YmdlpPKYUyyt93YsYHqSpFiiq9epa/Zos6f4EknitAUykk8xDp 4SBhScjbhQK11cetGIcu6o1Ro9+rubs9aXbn3rkwH3DmCFOlP/+VI+vEJ8vN0Wq4ujmc rJN3MkYc/+cErEJM+l4YhxFwRWNdhfU/z0NwKluauYaKvZFMDF0GFVKlVR5CWzoekd53 r9xVFr5krWwz3fftbrMQY4DdB/5U8EGUc8jWVx2ReD+De5GUOT5Dew/H7HKBfPZyDriL iuXA== X-Received: by 10.14.215.67 with SMTP id d43mr4788442eep.108.1386718251577; Tue, 10 Dec 2013 15:30:51 -0800 (PST) Received: from [192.168.0.100] (apn-5-60-128-63.dynamic.gprs.plus.pl. [5.60.128.63]) by mx.google.com with ESMTPSA id z42sm46692751eeo.17.2013.12.10.15.30.50 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 10 Dec 2013 15:30:50 -0800 (PST) Message-ID: <52A7A428.8070104@gmail.com> Date: Wed, 11 Dec 2013 00:30:48 +0100 From: Krzysztof Olszewski User-Agent: Mozilla/5.0 (X11; Linux i686; rv:17.0) Gecko/20130510 Thunderbird/17.0.6 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Problem with slow query with WHERE conditions with OR clause on primary keys Content-Type: multipart/alternative; boundary="------------010709040004020805050206" X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------010709040004020805050206 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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" "block_size";"8192" "cpu_index_tuple_cost";"0.005" "cpu_operator_cost";"0.0025" "cpu_tuple_cost";"0.01" "cursor_tuple_fraction";"0.1" "default_statistics_target";"1000" "enable_bitmapscan";"on" "enable_hashagg";"on" "enable_hashjoin";"on" "enable_indexscan";"on" "enable_material";"on" "enable_mergejoin";"on" "enable_nestloop";"on" "enable_seqscan";"on" "enable_sort";"on" "enable_tidscan";"on" "maintenance_work_mem";"262144" "max_connections";"10" "max_files_per_process";"1000" "max_locks_per_transaction";"64" "max_pred_locks_per_transaction";"64" "max_prepared_transactions";"10" "random_page_cost";"1.5" "seq_page_cost";"1" "shared_buffers";"65536" "temp_buffers";"1024" "work_mem";"131072" Thank you for your help. Kris Olszewski --------------010709040004020805050206 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 
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"
"block_size";"8192"
"cpu_index_tuple_cost";"0.005"
"cpu_operator_cost";"0.0025"
"cpu_tuple_cost";"0.01"
"cursor_tuple_fraction";"0.1"
"default_statistics_target";"1000"
"enable_bitmapscan";"on"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexscan";"on"
"enable_material";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"on"
"enable_sort";"on"
"enable_tidscan";"on"
"maintenance_work_mem";"262144"
"max_connections";"10"
"max_files_per_process";"1000"
"max_locks_per_transaction";"64"
"max_pred_locks_per_transaction";"64"
"max_prepared_transactions";"10"
"random_page_cost";"1.5"
"seq_page_cost";"1"
"shared_buffers";"65536"
"temp_buffers";"1024"
"work_mem";"131072"





Thank you for your help.
 

Kris Olszewski
--------------010709040004020805050206--