Hi,
thanks for the suggestion, but it didn't help. We have tried it earlier.
7500ms
http://explain.depesz.com/s/ctn
ALTER TABLE product_parent ALTER COLUMN parent_name SET STATISTICS 1000;
ALTER TABLE product ALTER COLUMN parent_id SET STATISTICS 1000;
ANALYZE product_parent;
ANALYZE product;
query was:
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
i've played with the query, and found an interesting behaviour: its
speed depends on value of limit:
select ... limit 2; => 1500ms
select ... limit 20; => 14ms (http://explain.depesz.com/s/4iL)
select ... limit 50; => 17ms
These were with high effective_cache_size (6GB). Somehow it uses good
planning in these cases.
If it helps i can send the db to repro (53M).
Any tips to try?
Thanks in advance,
Istvan