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
