bad planning with 75% effective_cache_size - Mailing list 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