Hi everybody,
thanks for the so many responses. :)
> On Thu, Apr 19, 2012 at 3:44 AM, Josh Berkus <josh@agliodbs.com>
>>> 7500ms
>>> http://explain.depesz.com/s/
>> This plan seems very odd -- doing individual index lookups on 2.8m rows
>> is not standard planner behavior. Can you confirm that all of your
>> other query cost parameters are the defaults?
Josh: i confirm the non-default values:
i've ran this query: http://wiki.postgresql.org/wiki/Server_Configuration
its result:
"version";"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"
"bytea_output";"escape"
"client_encoding";"UNICODE"
"client_min_messages";"log"
"effective_cache_size";"6GB"
"lc_collate";"en_US.UTF-8"
"lc_ctype";"en_US.UTF-8"
"listen_addresses";"*"
"log_directory";"/var/log/postgres"
"log_duration";"on"
"log_error_verbosity";"default"
"log_filename";"postgresql-%Y-%m-%d.log"
"log_line_prefix";"%t %u@%h %d %p %i "
"log_lock_waits";"on"
"log_min_duration_statement";"0"
"log_min_error_statement";"warning"
"log_min_messages";"warning"
"log_rotation_age";"15d"
"log_statement";"all"
"logging_collector";"on"
"max_connections";"100"
"max_stack_depth";"2MB"
"port";"5432"
"server_encoding";"UTF8"
"shared_buffers";"6024MB"
"TimeZone";"Europe/Budapest"
"wal_buffers";"16MB"
Ants:
> I'm not sure what to do about unique node overestimation, but I think
> it could be coaxed to be less optimistic about the limit by adding an
> optimization barrier and some selectivity decreasing clauses between
> the limit and the rest of the query:
>
> select * from (
> select distinct product_code from product p_
> inner join product_parent par_ on p_.parent_id=par_.id
> where par_.parent_name like 'aa%'
> offset 0 -- optimization barrier
> ) as x
> where product_code = product_code -- reduce selectivity estimate by 200x
> limit 2;
Its planning: http://explain.depesz.com/s/eF3h
1700ms
Віталій:
> How about
>
> with par_ as (select * from product_parent where parent_name like 'aa%' )
> select distinct product_code from product p_
> inner join par_ on p_.parent_id=par_.id
> limit 2
Its planning: http://explain.depesz.com/s/YIS
All suggestions are welcome,
Istvan
