From: Krzysztof Olszewski <kolszew73@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Problem with slow query with WHERE conditions with OR clause on primary keys
Date: Wed, 11 Dec 2013 00:30:48 +0100
Message-ID: <52A7A428.8070104@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: kolszew73@gmail.com
Subject: Re: Problem with slow query with WHERE conditions with OR clause on primary keys
In-Reply-To: <52A7A428.8070104@gmail.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