bad planning with 75% effective_cache_size

From: Istvan Endredy <istvan(dot)endredy(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: bad planning with 75% effective_cache_size
Date: 2012-04-03 15:11:55
Message-ID: CAEcxehpLL5N1B_w7swC9HJjAh78GZiENyn4OUBrpqPa=HyzGmQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

i've ran into a planning problem.

Dedicated PostgreSQL Server:
"PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-46), 64-bit"
Memory: 8GB
4CPUs

The problem is reduced to the following: there are 2 tables:
-product (3millions rows, 1GB)
-product_parent (3000rows, 0.5MB)

If effective_cache_size has a greater value (6GB), this select has a bad
planning and long query time (2000ms):

select distinct product_code from product p_
inner join product_parent par_ on p_.parent_id=par_.id
where par_.parent_name like 'aa%' limit 2

If effective_cache_size is smaller (32MB), planning is ok and query is
fast. (10ms)
In the worst case (effective_cache_size=6GB) the speed depends on the value
of 'limit' (in select): if it is smaller, query is slower. (12ms)

Good planning: http://explain.depesz.com/s/0FD
"Limit (cost=3704.00..3704.02 rows=2 width=5) (actual time=0.215..0.217
rows=1 loops=1)"
" -> HashAggregate (cost=3704.00..3712.85 rows=885 width=5) (actual
time=0.213..0.215 rows=1 loops=1)"
" -> Nested Loop (cost=41.08..3701.79 rows=885 width=5) (actual
time=0.053..0.175 rows=53 loops=1)"
" -> Index Scan using telepulesbugreport_nev_idx on
product_parent par_ (cost=0.00..8.27 rows=1 width=4) (actual
time=0.016..0.018 rows=1 loops=1)"
" Index Cond: (((parent_name)::text ~>=~ 'aa'::text) AND
((parent_name)::text ~<~ 'ab'::text))"
" Filter: ((parent_name)::text ~~ 'aa%'::text)"
" -> Bitmap Heap Scan on product p_ (cost=41.08..3680.59
rows=1034 width=9) (actual time=0.033..0.125 rows=53 loops=1)"
" Recheck Cond: (parent_id = par_.id)"
" -> Bitmap Index Scan on
kapubugreport_telepules_id_idx (cost=0.00..40.82 rows=1034 width=0)
(actual time=0.024..0.024 rows=53 loops=1)"
" Index Cond: (parent_id = par_.id)"
"Total runtime: 0.289 ms"

Bad planning: http://explain.depesz.com/s/yBh
"Limit (cost=0.00..854.37 rows=2 width=5) (actual time=1799.209..4344.041
rows=1 loops=1)"
" -> Unique (cost=0.00..378059.84 rows=885 width=5) (actual
time=1799.207..4344.038 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..378057.63 rows=885 width=5) (actual
time=1799.204..4344.020 rows=53 loops=1)"
" Join Filter: (p_.parent_id = par_.id)"
" -> Index Scan using kapubugreport_irsz_telepules_id_idx on
product p_ (cost=0.00..334761.59 rows=2885851 width=9) (actual
time=0.015..1660.449 rows=2884172 loops=1)"
" -> Materialize (cost=0.00..8.27 rows=1 width=4) (actual
time=0.000..0.000 rows=1 loops=2884172)"
" -> Index Scan using telepulesbugreport_nev_idx on
product_parent par_ (cost=0.00..8.27 rows=1 width=4) (actual
time=0.013..0.014 rows=1 loops=1)"
" Index Cond: (((parent_name)::text ~>=~
'aa'::text) AND ((parent_name)::text ~<~ 'ab'::text))"
" Filter: ((parent_name)::text ~~ 'aa%'::text)"
"Total runtime: 4344.083 ms"

schema:

CREATE TABLE product
(
id serial NOT NULL,
parent_id integer NOT NULL,
product_code character varying COLLATE pg_catalog."C" NOT NULL,
product_name character varying NOT NULL
)
WITH (
OIDS=FALSE
);
ALTER TABLE product
OWNER TO aa;

CREATE INDEX product_code_parent_id_idx
ON product
USING btree
(product_code COLLATE pg_catalog."C" , parent_id );

CREATE INDEX product_name_idx
ON product
USING btree
(product_name COLLATE pg_catalog."default" );

CREATE INDEX product_parent_id_idx
ON product
USING btree
(parent_id );

CREATE INDEX product_parent_id_ocde_idx
ON product
USING btree
(parent_id , product_code COLLATE pg_catalog."C" );

CREATE TABLE product_parent
(
id serial NOT NULL,
parent_name character varying NOT NULL,
CONSTRAINT telepulesbugreport_pkey PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE product_parent
OWNER TO aa;

CREATE INDEX product_parent_name_idx
ON product_parent
USING btree
(parent_name COLLATE pg_catalog."default" varchar_pattern_ops);

I hope you can help me... :)
Best Regads,
Istvan

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ofer Israeli 2012-04-03 15:24:07 Re: TCP Overhead on Local Loopback
Previous Message Robert Haas 2012-04-03 14:57:43 Re: ...WHERE TRUE" condition in union results in bad query pla