Início / user-8017

vyegorov's questions

Martin Hope
vyegorov
Asked: 2014-09-25 23:23:51 +0800 CST
  • 1

Tenho o seguinte caso de teste:

CREATE SCHEMA IF NOT EXISTS long;
SET search_path TO long,pg_temp,pg_catalog;
CREATE TABLE long WITH (autovacuum_enabled=off) AS
SELECT id, (random()*1000)::int AS key, random() AS value1, random() AS value2,
       repeat((random()*9)::int::text, 312) AS long
  FROM generate_series(1,10000000) id;
CREATE INDEX i_long_value1 ON long (value1);
CREATE INDEX i_long_wo ON long(key,value2 DESC);
VACUUM ANALYZE long;

Agora, se eu explicar a seguinte consulta:

SELECT * FROM long WHERE key=5 AND value1 BETWEEN 0.5 AND 0.6 ORDER BY value2 DESC;

Eu sempre recebo o seguinte plano . Isso é estranho, pois tenho um índice correspondente i_long_wo. Então eu uso: SET enable_bitmapscan TO off;e isso resulta em um plano muito melhor . Isso é reproduzível em todas as instâncias PG que experimentei (win64, HP-UX, MacOS) e não apenas no 9.4beta2. Além disso, executei essas consultas várias vezes antes de examinar os planos, para garantir que todas as informações sejam armazenadas em cache, você pode verificar Buffers: shared hitas entradas nos planos apresentados.

Estou executando em um (mesmo efeito observado em 9.1-9.4b2 no MacOS também):

postgres=# SELECT version();
                            version
----------------------------------------------------------------
 PostgreSQL 9.4beta2, compiled by Visual C++ build 1800, 64-bit

postgres=# SELECT name,setting,unit,source FROM pg_settings
            WHERE NOT source IN ('default','override');
             name             |      setting       | unit |        source
------------------------------+--------------------+------+----------------------
 application_name             | psql               |      | client
 bgwriter_delay               | 50                 | ms   | configuration file
 bgwriter_lru_maxpages        | 350                |      | configuration file
 checkpoint_completion_target | 0.9                |      | configuration file
 checkpoint_segments          | 100                |      | configuration file
 client_encoding              | WIN1252            |      | client
 DateStyle                    | ISO, MDY           |      | configuration file
 default_text_search_config   | pg_catalog.english |      | configuration file
 dynamic_shared_memory_type   | windows            |      | configuration file
 effective_cache_size         | 524288             | 8kB  | configuration file
 lc_messages                  | C                  |      | configuration file
 lc_monetary                  | C                  |      | configuration file
 lc_numeric                   | C                  |      | configuration file
 lc_time                      | C                  |      | configuration file
 listen_addresses             | *                  |      | configuration file
 log_destination              | stderr             |      | configuration file
 log_line_prefix              | %t                 |      | configuration file
 log_timezone                 | Europe/Helsinki    |      | configuration file
 logging_collector            | on                 |      | configuration file
 max_connections              | 100                |      | configuration file
 max_stack_depth              | 2048               | kB   | environment variable
 port                         | 5432               |      | configuration file
 shared_buffers               | 131072             | 8kB  | configuration file
 TimeZone                     | Europe/Helsinki    |      | configuration file
 wal_buffers                  | 2048               | 8kB  | configuration file
 work_mem                     | 16384              | kB   | configuration file
(26 rows)

A instância do Windows está usando o instalador 9.4beta2 do EDB, atualizarei os detalhes da compilação assim que estiver por perto. Configuração usada para criar a instância do MacOS:

CONFIGURE = '--prefix=/usr/local/Cellar/postgresql-9.4/9.4beta2' '--enable-dtrace' \
  '--with-bonjour' '--with-gssapi' '--with-ldap' '--with-libxml' \
  '--with-libxslt' '--with-openssl' '--with-uuid=e2fs' '--with-pam' \
  '--with-perl' '--with-python' '--with-tcl' \
'CC=/usr/bin/clang' \
'CFLAGS=-Os -w -pipe -march=native -mmacosx-version-min=10.9' \
'LDFLAGS=-L/usr/local/opt/readline/lib -L/usr/local/opt/e2fsprogs/lib \
  -L/usr/local/opt/gettext/lib -L/usr/local/lib \
  -Wl,-headerpad_max_install_names' \
'CPPFLAGS=-I/usr/local/opt/readline/include -I/usr/local/opt/e2fsprogs/include \
  -I/usr/local/opt/gettext/include'
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 \
  -lz -lreadline -lm

Então, estou me perguntando - por que o BitmapIndexScan obteve estimativas melhores?

Acertei este post de Tom Lane (bastante antigo) e, pelo que entendi, a maneira recomendada é ajustar os *_costparâmetros. Mas o problema é que o custo é afetado pelo número de páginas a mais, isso é visto pelo número de buffers que o PostgreSQL toca durante a execução, eles são comparáveis ​​para ambos os planos e são maiores que o número de registros retornados. Portanto, o ajuste random_page_costreduz o custo para ambos os planos e o IndexScan nunca vence.

Tentei aumentar cpu_index_tuple_cost, tive que aumentar 0.02para fazer o planejador parar de usar BitmapAnd. Nesta configuração, a variação do BitmapIndexScan parece muito melhor, mas ainda perde para o IndexScan.

É possível fazer o planejador fornecer uma estimativa melhor para o IndexScan aqui?

postgresql index