Estou tentando descobrir por que uma tabela minha está usando uma varredura de heap de bitmap quando uma varredura de índice é drasticamente mais rápida.

Mesa:

webarchive=# \d web_pages
                                               Table "public.web_pages"
      Column       |            Type             |                              Modifiers
-------------------+-----------------------------+---------------------------------------------------------------------
 id                | bigint                      | not null default nextval('web_pages_id_seq'::regclass)
 state             | dlstate_enum                | not null
 errno             | integer                     |
 url               | text                        | not null
 starturl          | text                        | not null
 netloc            | text                        | not null
 file              | bigint                      |
 priority          | integer                     | not null
 distance          | integer                     | not null
 is_text           | boolean                     |
 limit_netloc      | boolean                     |
 title             | citext                      |
 mimetype          | text                        |
 type              | itemtype_enum               |
 content           | text                        |
 fetchtime         | timestamp without time zone |
 addtime           | timestamp without time zone |
 normal_fetch_mode | boolean                     | default true
 ignoreuntiltime   | timestamp without time zone | not null default '1970-01-01 00:00:00'::timestamp without time zone
Indexes:
    "web_pages_pkey" PRIMARY KEY, btree (id)
    "ix_web_pages_url" UNIQUE, btree (url)
    "ix_web_pages_distance" btree (distance)
    "ix_web_pages_fetchtime" btree (fetchtime)
    "ix_web_pages_id" btree (id)
    "ix_web_pages_id_state" btree (id, state)
    "ix_web_pages_netloc" btree (netloc)
    "ix_web_pages_priority" btree (priority)
    "ix_web_pages_state" btree (state)
    "web_pages_netloc_fetchtime_idx" btree (netloc, fetchtime)
    "web_pages_netloc_id_idx" btree (netloc, id)
Foreign-key constraints:
    "web_pages_file_fkey" FOREIGN KEY (file) REFERENCES web_files(id)
Tablespace: "main_webarchive_tablespace"

Consulta:

EXPLAIN ANALYZE UPDATE
    web_pages
SET
    state = 'new'
WHERE
    (state = 'fetching' OR state = 'processing')
AND
    id <= 150000000;

Nesse caso, como tenho um índice de cobertura ( ix_web_pages_id_state), esperaria que o planejador de consulta fizesse uma verificação apenas de índice. No entanto, em vez disso, está gerando uma varredura de heap de bitmap, que é drasticamente mais lenta:

                                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on web_pages  (cost=524.06..532.09 rows=2 width=671) (actual time=2356.900..2356.900 rows=0 loops=1)
   ->  Bitmap Heap Scan on web_pages  (cost=524.06..532.09 rows=2 width=671) (actual time=2356.896..2356.896 rows=0 loops=1)
         Recheck Cond: (((state = 'fetching'::dlstate_enum) OR (state = 'processing'::dlstate_enum)) AND (id <= 150000000))
         Heap Blocks: exact=6
         ->  BitmapAnd  (cost=524.06..524.06 rows=2 width=0) (actual time=2353.388..2353.388 rows=0 loops=1)
               ->  BitmapOr  (cost=151.98..151.98 rows=6779 width=0) (actual time=2021.635..2021.636 rows=0 loops=1)
                     ->  Bitmap Index Scan on ix_web_pages_state  (cost=0.00..147.41 rows=6779 width=0) (actual time=2021.616..2021.617 rows=11668131 loops=1)
                           Index Cond: (state = 'fetching'::dlstate_enum)
                     ->  Bitmap Index Scan on ix_web_pages_state  (cost=0.00..4.57 rows=1 width=0) (actual time=0.015..0.016 rows=0 loops=1)
                           Index Cond: (state = 'processing'::dlstate_enum)
               ->  Bitmap Index Scan on web_pages_pkey  (cost=0.00..371.83 rows=16435 width=0) (actual time=0.046..0.047 rows=205 loops=1)
                     Index Cond: (id <= 150000000)
 Planning time: 0.232 ms
 Execution time: 2406.234 ms
(14 rows)

Se eu forçá-lo a não fazer uma varredura de heap de bitmap (por set enable_bitmapscan to off;), ele gera um plano MUITO mais rápido:

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Update on web_pages  (cost=0.56..38591.75 rows=2 width=671) (actual time=0.284..0.285 rows=0 loops=1)
   ->  Index Scan using web_pages_pkey on web_pages  (cost=0.56..38591.75 rows=2 width=671) (actual time=0.281..0.281 rows=0 loops=1)
         Index Cond: (id <= 150000000)
         Filter: ((state = 'fetching'::dlstate_enum) OR (state = 'processing'::dlstate_enum))
         Rows Removed by Filter: 181
 Planning time: 0.190 ms
 Execution time: 0.334 ms
(7 rows)

Eu executei novamente uma análise de vácuo para ver se era possível que as estatísticas da tabela estivessem desatualizadas, mas isso não parecia ter nenhum benefício. Além disso, os itens acima são depois de reexecutar a mesma consulta várias vezes, então acho que o cache também não deve ser relevante.

Como posso induzir o planejador a gerar um plano de melhor desempenho aqui?

Edit: Conforme sugerido nos comentários, adicionei um index "ix_web_pages_state_id" btree (state, id). Infelizmente, não ajudou.

Eu também experimentei reduzir o random_page_cost(para tão baixo quanto 0,5), bem como aumentar a meta de estatísticas, nenhuma das quais teve nenhum efeito.

Edição adicional - Removendo a condição OR:

EXPLAIN ANALYZE UPDATE
    web_pages
SET
    state = 'new'
WHERE
    state = 'fetching'
AND
    id <= 150000000;

Rendimentos:

                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on web_pages  (cost=311.83..315.84 rows=1 width=589) (actual time=2574.654..2574.655 rows=0 loops=1)
   ->  Bitmap Heap Scan on web_pages  (cost=311.83..315.84 rows=1 width=589) (actual time=2574.650..2574.651 rows=0 loops=1)
         Recheck Cond: ((id <= 150000000) AND (state = 'fetching'::dlstate_enum))
         Heap Blocks: exact=6
         ->  BitmapAnd  (cost=311.83..311.83 rows=1 width=0) (actual time=2574.556..2574.556 rows=0 loops=1)
               ->  Bitmap Index Scan on web_pages_pkey  (cost=0.00..49.60 rows=1205 width=0) (actual time=0.679..0.680 rows=726 loops=1)
                     Index Cond: (id <= 150000000)
               ->  Bitmap Index Scan on ix_web_pages_state  (cost=0.00..261.98 rows=7122 width=0) (actual time=2519.950..2519.951 rows=11873888 loops=1)
                     Index Cond: (state = 'fetching'::dlstate_enum)

Edição adicional - MOAR WEIRDNESS:

Eu reescrevi a consulta para usar uma subconsulta:

EXPLAIN ANALYZE UPDATE
    web_pages
SET
    state = 'new'
WHERE
    (state = 'fetching' OR state = 'processing')
AND
    id IN (
        SELECT 
            id 
        FROM 
            web_pages 
        WHERE 
            id <= 150000000
    );

e isso produz um plano de execução resultante que supera todos os outros até agora. às vezes :

                                                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on web_pages  (cost=1.12..13878.31 rows=1 width=595) (actual time=2.773..2.774 rows=0 loops=1)
   ->  Nested Loop  (cost=1.12..13878.31 rows=1 width=595) (actual time=2.772..2.773 rows=0 loops=1)
         ->  Index Scan using web_pages_pkey on web_pages web_pages_1  (cost=0.56..3533.34 rows=1205 width=14) (actual time=0.000..0.602 rows=181 loops=1)
               Index Cond: (id <= 150000000)
         ->  Index Scan using web_pages_pkey on web_pages  (cost=0.56..8.58 rows=1 width=585) (actual time=0.010..0.010 rows=0 loops=181)
               Index Cond: (id = web_pages_1.id)
               Filter: ((state = 'fetching'::dlstate_enum) OR (state = 'processing'::dlstate_enum))
               Rows Removed by Filter: 1
 Planning time: 0.891 ms
 Execution time: 2.894 ms
(10 rows)

Update on web_pages  (cost=21193.19..48917.78 rows=2 width=595)
  ->  Hash Semi Join  (cost=21193.19..48917.78 rows=2 width=595)
        Hash Cond: (web_pages.id = web_pages_1.id)
        ->  Bitmap Heap Scan on web_pages  (cost=270.14..27976.00 rows=7126 width=585)
              Recheck Cond: ((state = 'fetching'::dlstate_enum) OR (state = 'processing'::dlstate_enum))
              ->  BitmapOr  (cost=270.14..270.14 rows=7126 width=0)
                    ->  Bitmap Index Scan on ix_web_pages_state  (cost=0.00..262.01 rows=7126 width=0)
                          Index Cond: (state = 'fetching'::dlstate_enum)
                    ->  Bitmap Index Scan on ix_web_pages_state  (cost=0.00..4.57 rows=1 width=0)
                          Index Cond: (state = 'processing'::dlstate_enum)
        ->  Hash  (cost=20834.15..20834.15 rows=7112 width=14)
              ->  Index Scan using web_pages_pkey on web_pages web_pages_1  (cost=0.56..20834.15 rows=7112 width=14)
                    Index Cond: ((id > 1883250000) AND (id <= 1883300000))

Eu não tenho ideia do que está acontecendo, neste momento. Tudo o que sei é que todos os casos são corrigidos por set enable_bitmapscan to off;.

Ok, a transação extremamente longa que eu estava executando terminou ontem à noite e consegui executar uma VACUUM VERBOSE ANALYZEna mesa:

webarchive=# VACUUM ANALYZE VERBOSE web_pages;
INFO:  vacuuming "public.web_pages"
INFO:  scanned index "ix_web_pages_distance" to remove 33328301 row versions
DETAIL:  CPU 6.85s/21.21u sec elapsed 171.28 sec
INFO:  scanned index "ix_web_pages_fetchtime" to remove 33328301 row versions
DETAIL:  CPU 6.20s/25.28u sec elapsed 186.53 sec
INFO:  scanned index "ix_web_pages_id" to remove 33328301 row versions
DETAIL:  CPU 7.37s/29.56u sec elapsed 226.49 sec
INFO:  scanned index "ix_web_pages_netloc" to remove 33328301 row versions
DETAIL:  CPU 8.47s/41.44u sec elapsed 260.50 sec
INFO:  scanned index "ix_web_pages_priority" to remove 33328301 row versions
DETAIL:  CPU 5.65s/16.35u sec elapsed 180.78 sec
INFO:  scanned index "ix_web_pages_state" to remove 33328301 row versions
DETAIL:  CPU 4.51s/21.14u sec elapsed 189.60 sec
INFO:  scanned index "ix_web_pages_url" to remove 33328301 row versions
DETAIL:  CPU 26.59s/78.52u sec elapsed 969.99 sec
INFO:  scanned index "web_pages_netloc_fetchtime_idx" to remove 33328301 row versions
DETAIL:  CPU 8.23s/48.39u sec elapsed 301.37 sec
INFO:  scanned index "web_pages_netloc_id_idx" to remove 33328301 row versions
DETAIL:  CPU 15.52s/43.25u sec elapsed 423.68 sec
INFO:  scanned index "web_pages_pkey" to remove 33328301 row versions
DETAIL:  CPU 8.12s/33.43u sec elapsed 215.93 sec
INFO:  scanned index "ix_web_pages_id_state" to remove 33328301 row versions
DETAIL:  CPU 8.22s/33.26u sec elapsed 214.43 sec
INFO:  scanned index "ix_web_pages_state_id" to remove 33328301 row versions
DETAIL:  CPU 6.01s/28.04u sec elapsed 174.19 sec
INFO:  "web_pages": removed 33328301 row versions in 3408348 pages
DETAIL:  CPU 89.90s/50.24u sec elapsed 1928.70 sec
INFO:  index "ix_web_pages_distance" now contains 29463963 row versions in 215671 pages
DETAIL:  33328301 index row versions were removed.
32914 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ix_web_pages_fetchtime" now contains 29463982 row versions in 253375 pages
DETAIL:  33328301 index row versions were removed.
40460 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ix_web_pages_id" now contains 29464000 row versions in 238212 pages
DETAIL:  33328301 index row versions were removed.
21081 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ix_web_pages_netloc" now contains 29464025 row versions in 358150 pages
DETAIL:  33328301 index row versions were removed.
99235 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ix_web_pages_priority" now contains 29464032 row versions in 214923 pages
DETAIL:  33328301 index row versions were removed.
21451 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ix_web_pages_state" now contains 29466359 row versions in 215150 pages
DETAIL:  33328301 index row versions were removed.
81340 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ix_web_pages_url" now contains 29466350 row versions in 1137027 pages
DETAIL:  33197635 index row versions were removed.
236405 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "web_pages_netloc_fetchtime_idx" now contains 29466381 row versions in 539255 pages
DETAIL:  33328301 index row versions were removed.
220594 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "web_pages_netloc_id_idx" now contains 29466392 row versions in 501276 pages
DETAIL:  33328301 index row versions were removed.
144217 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "web_pages_pkey" now contains 29466394 row versions in 236560 pages
DETAIL:  33173411 index row versions were removed.
20559 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ix_web_pages_id_state" now contains 29466415 row versions in 256699 pages
DETAIL:  33328301 index row versions were removed.
27194 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ix_web_pages_state_id" now contains 29466435 row versions in 244076 pages
DETAIL:  33328301 index row versions were removed.
91918 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "web_pages": found 33339704 removable, 29367176 nonremovable row versions in 4224021 out of 4231795 pages
DETAIL:  2541 dead row versions cannot be removed yet.
There were 2079389 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 330.54s/537.34u sec elapsed 7707.90 sec.
INFO:  vacuuming "pg_toast.pg_toast_705758310"
INFO:  scanned index "pg_toast_705758310_index" to remove 7184381 row versions
DETAIL:  CPU 7.32s/13.70u sec elapsed 240.71 sec
INFO:  "pg_toast_705758310": removed 7184381 row versions in 2271192 pages
DETAIL:  CPU 62.81s/46.41u sec elapsed 1416.12 sec
INFO:  index "pg_toast_705758310_index" now contains 114558558 row versions in 338256 pages
DETAIL:  7184381 index row versions were removed.
2033 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_705758310": found 7184381 removable, 40907769 nonremovable row versions in 11388831 out of 29033065 pages
DETAIL:  5 dead row versions cannot be removed yet.
There were 74209 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 433.26s/247.73u sec elapsed 8444.85 sec.
INFO:  analyzing "public.web_pages"
INFO:  "web_pages": scanned 600000 of 4232727 pages, containing 4191579 live rows and 4552 dead rows; 600000 rows in sample, 29569683 estimated total rows
VACUUM

Ele ainda está gerando a consulta não somente de índice, embora o tempo de execução seja muito mais alinhado com a consulta somente de índice. Não entendo porque o comportamento mudou tanto. Ter uma consulta de execução muito longa causa tanta sobrecarga?

webarchive=# EXPLAIN ANALYZE UPDATE
        web_pages
    SET
        state = 'new'
    WHERE
        (state = 'fetching' OR state = 'processing')
    AND
        id IN (
            SELECT
                id
            FROM
                web_pages
            WHERE
                id > 1883250000
            AND
                id <= 1883300000
        );
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on web_pages  (cost=36.00..9936.00 rows=1 width=594) (actual time=37.856..37.857 rows=0 loops=1)
   ->  Nested Loop Semi Join  (cost=36.00..9936.00 rows=1 width=594) (actual time=37.852..37.853 rows=0 loops=1)
         ->  Bitmap Heap Scan on web_pages  (cost=35.44..3167.00 rows=788 width=584) (actual time=23.984..31.489 rows=2321 loops=1)
               Recheck Cond: ((state = 'fetching'::dlstate_enum) OR (state = 'processing'::dlstate_enum))
               Heap Blocks: exact=2009
               ->  BitmapOr  (cost=35.44..35.44 rows=788 width=0) (actual time=22.347..22.348 rows=0 loops=1)
                     ->  Bitmap Index Scan on ix_web_pages_state  (cost=0.00..30.47 rows=788 width=0) (actual time=22.326..22.327 rows=9202 loops=1)
                           Index Cond: (state = 'fetching'::dlstate_enum)
                     ->  Bitmap Index Scan on ix_web_pages_state_id  (cost=0.00..4.57 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1)
                           Index Cond: (state = 'processing'::dlstate_enum)
         ->  Index Scan using ix_web_pages_id_state on web_pages web_pages_1  (cost=0.56..8.58 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=2321)
               Index Cond: ((id = web_pages.id) AND (id > 1883250000) AND (id <= 1883300000))
 Planning time: 2.677 ms
 Execution time: 37.945 ms
(14 rows)

Curiosamente, o valor do deslocamento de ID parece afetar o planejamento:

webarchive=# EXPLAIN ANALYZE UPDATE
        web_pages
    SET
        state = 'new'
    WHERE
        (state = 'fetching' OR state = 'processing')
    AND
        id IN (
            SELECT
                id
            FROM
                web_pages
            WHERE
                id >  149950000
            AND
                id <= 150000000
        );
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on web_pages  (cost=1.12..17.18 rows=1 width=594) (actual time=0.030..0.031 rows=0 loops=1)
   ->  Nested Loop  (cost=1.12..17.18 rows=1 width=594) (actual time=0.026..0.028 rows=0 loops=1)
         ->  Index Scan using ix_web_pages_id_state on web_pages web_pages_1  (cost=0.56..8.58 rows=1 width=14) (actual time=0.022..0.024 rows=0 loops=1)
               Index Cond: ((id > 149950000) AND (id <= 150000000))
         ->  Index Scan using ix_web_pages_id_state on web_pages  (cost=0.56..8.59 rows=1 width=584) (never executed)
               Index Cond: (id = web_pages_1.id)
               Filter: ((state = 'fetching'::dlstate_enum) OR (state = 'processing'::dlstate_enum))
 Planning time: 1.531 ms
 Execution time: 0.155 ms
(9 rows)

O planejador de consultas leva em consideração o valor dos parâmetros da consulta em seu planejamento? Eu teria pensado que o planejamento seria agnóstico dos parâmetros de consulta, mas considerando isso agora, usar os parâmetros para melhorar o planejamento faz sentido, então posso vê-lo funcionando dessa maneira.

Curiosamente, a varredura de bitmap parece ter muito mais desempenho agora;

webarchive=# set enable_bitmapscan to off;
SET
webarchive=#     EXPLAIN ANALYZE UPDATE
        web_pages
    SET
        state = 'new'
    WHERE
        (state = 'fetching' OR state = 'processing')
    AND
        id IN (
            SELECT
                id
            FROM
                web_pages
            WHERE
                id > 1883250000
            AND
                id <= 1883300000
        );
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on web_pages  (cost=1.12..82226.59 rows=1 width=594) (actual time=66.993..66.994 rows=0 loops=1)
   ->  Nested Loop  (cost=1.12..82226.59 rows=1 width=594) (actual time=66.992..66.993 rows=0 loops=1)
         ->  Index Scan using web_pages_pkey on web_pages web_pages_1  (cost=0.56..21082.82 rows=7166 width=14) (actual time=0.055..20.206 rows=8567 loops=1)
               Index Cond: ((id > 1883250000) AND (id <= 1883300000))
         ->  Index Scan using web_pages_pkey on web_pages  (cost=0.56..8.52 rows=1 width=584) (actual time=0.004..0.004 rows=0 loops=8567)
               Index Cond: (id = web_pages_1.id)
               Filter: ((state = 'fetching'::dlstate_enum) OR (state = 'processing'::dlstate_enum))
               Rows Removed by Filter: 1
 Planning time: 1.963 ms
 Execution time: 67.112 ms
(10 rows)

webarchive=# set enable_bitmapscan to on;
SET
webarchive=#     EXPLAIN ANALYZE UPDATE
        web_pages
    SET
        state = 'new'
    WHERE
        (state = 'fetching' OR state = 'processing')
    AND
        id IN (
            SELECT
                id
            FROM
                web_pages
            WHERE
                id > 1883250000
            AND
                id <= 1883300000
        );
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on web_pages  (cost=36.00..9936.00 rows=1 width=594) (actual time=23.331..23.331 rows=0 loops=1)
   ->  Nested Loop Semi Join  (cost=36.00..9936.00 rows=1 width=594) (actual time=23.327..23.328 rows=0 loops=1)
         ->  Bitmap Heap Scan on web_pages  (cost=35.44..3167.00 rows=788 width=584) (actual time=6.727..17.027 rows=1966 loops=1)
               Recheck Cond: ((state = 'fetching'::dlstate_enum) OR (state = 'processing'::dlstate_enum))
               Heap Blocks: exact=3825
               ->  BitmapOr  (cost=35.44..35.44 rows=788 width=0) (actual time=3.499..3.499 rows=0 loops=1)
                     ->  Bitmap Index Scan on ix_web_pages_state  (cost=0.00..30.47 rows=788 width=0) (actual time=3.471..3.472 rows=21996 loops=1)
                           Index Cond: (state = 'fetching'::dlstate_enum)
                     ->  Bitmap Index Scan on ix_web_pages_state_id  (cost=0.00..4.57 rows=1 width=0) (actual time=0.022..0.023 rows=0 loops=1)
                           Index Cond: (state = 'processing'::dlstate_enum)
         ->  Index Scan using ix_web_pages_id_state on web_pages web_pages_1  (cost=0.56..8.58 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=1966)
               Index Cond: ((id = web_pages.id) AND (id > 1883250000) AND (id <= 1883300000))
 Planning time: 0.774 ms
 Execution time: 23.425 ms
(14 rows)

So I think the issue was just the index having LOTS of rows that were no longer valid, and the process of filtering those was the primary time cost. The underlying issue here is, (I think) the way the MVCC system interacts with the VACUUM system in the context of extremely long running transactions.

It would make sense (in retrospect), that entries cannot be removed from an index until every single transaction that could use that index has been completed. From the documentation:

But there is an additional requirement for any table scan in PostgreSQL: it must verify that each retrieved row be "visible" to the query's MVCC snapshot, as discussed in Chapter 13. Visibility information is not stored in index entries, only in heap entries; so at first glance it would seem that every row retrieval would require a heap access anyway. And this is indeed the case, if the table row has been modified recently. However, for seldom-changing data there is a way around this problem.

In this case, I started a db dump, and then went on and did a bunch of cleanup (which involved a LOT of row churn). That would lead to lots of heap lookups for each index query, since the index contained lots of now-deleted rows.

This is mostly hypothetical, though, since I don't have the resources to try to recreate the situation.

Anyways, @jjanes's hint about long-running queries was the key to finding my way down the rabbit hole here.

Eu tenho um banco de dados com uma tabela extremamente grande no postgresql.

Estou ciente de que a única maneira de reduzir o tamanho do disco é VACUUM FULL, mas não posso fazer isso, pois não tenho espaço livre suficiente (é uma tabela de 920 GB em um disco de 1 TB e não posso pagar outro ATM SSD de 1 TB).

No entanto, eu executei VACUUM VERBOSE ANALYZE web_pagese foi concluído, mas o tamanho da tabela (conforme relatado em psql) não foi reduzido.

Basicamente, existe uma maneira de reduzir uma tabela sem VACUUM FULLou completar o dump/load? Eu tenho espaço para um dump/load, mas neste momento eu espero que demore mais de uma semana.

Saída de vácuo:

webarchive=# VACUUM VERBOSE ANALYZE web_pages;
INFO:  vacuuming "public.web_pages"
INFO:  scanned index "ix_web_pages_distance_filtered" to remove 145580643 row versions
DETAIL:  CPU 4.46s/165.77u sec elapsed 324.63 sec
INFO:  scanned index "ix_web_pages_netloc" to remove 145580643 row versions
DETAIL:  CPU 40.65s/4686.88u sec elapsed 5387.13 sec
INFO:  scanned index "ix_web_pages_priority" to remove 145580643 row versions
DETAIL:  CPU 29.59s/1018.71u sec elapsed 1452.67 sec
INFO:  scanned index "ix_web_pages_state" to remove 145580643 row versions
DETAIL:  CPU 22.08s/303.12u sec elapsed 712.94 sec
INFO:  scanned index "ix_web_pages_url" to remove 145580643 row versions
DETAIL:  CPU 283.45s/673.39u sec elapsed 7583.39 sec
INFO:  scanned index "web_pages_pkey" to remove 145580643 row versions
DETAIL:  CPU 51.69s/90.19u sec elapsed 1461.37 sec
INFO:  scanned index "ix_web_pages_id" to remove 145580643 row versions
DETAIL:  CPU 63.13s/99.77u sec elapsed 1529.22 sec
INFO:  scanned index "web_pages_netloc_fetchtime_idx" to remove 145580643 row versions
DETAIL:  CPU 77.04s/5080.52u sec elapsed 6287.14 sec
INFO:  scanned index "id_web_pages_id_state" to remove 145580643 row versions
DETAIL:  CPU 64.52s/107.81u sec elapsed 1695.07 sec
INFO:  scanned index "web_pages_fetchtime_idx" to remove 145580643 row versions
DETAIL:  CPU 12.06s/99.66u sec elapsed 408.36 sec
INFO:  "web_pages": removed 145580643 row versions in 8584664 pages
DETAIL:  CPU 226.70s/140.17u sec elapsed 5019.28 sec
INFO:  index "ix_web_pages_distance_filtered" now contains 16007295 row versions in 814166 pages
DETAIL:  38738938 index row versions were removed.
570268 index pages have been deleted, 385915 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "ix_web_pages_netloc" now contains 27370778 row versions in 3181634 pages
DETAIL:  67244989 index row versions were removed.
2669376 index pages have been deleted, 1876620 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_priority" now contains 27370960 row versions in 2006220 pages
DETAIL:  67218177 index row versions were removed.
1056657 index pages have been deleted, 786603 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO:  index "ix_web_pages_state" now contains 27370969 row versions in 1532024 pages
DETAIL:  67244989 index row versions were removed.
986826 index pages have been deleted, 700367 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_url" now contains 27382514 row versions in 7555366 pages
DETAIL:  78562001 index row versions were removed.
4290425 index pages have been deleted, 225461 are currently reusable.
CPU 0.02s/0.00u sec elapsed 0.04 sec.
INFO:  index "web_pages_pkey" now contains 27401242 row versions in 2421605 pages
DETAIL:  78000787 index row versions were removed.
1068399 index pages have been deleted, 373558 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_id" now contains 27411627 row versions in 2874706 pages
DETAIL:  82612172 index row versions were removed.
1290296 index pages have been deleted, 442226 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "web_pages_netloc_fetchtime_idx" now contains 27556711 row versions in 4482440 pages
DETAIL:  80962513 index row versions were removed.
3373490 index pages have been deleted, 1873800 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "id_web_pages_id_state" now contains 27558627 row versions in 3094617 pages
DETAIL:  81497647 index row versions were removed.
1735454 index pages have been deleted, 631419 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "web_pages_fetchtime_idx" now contains 27559941 row versions in 656103 pages
DETAIL:  67710984 index row versions were removed.
228974 index pages have been deleted, 95938 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "web_pages": found 32297714 removable, 26459019 nonremovable row versions in 14298550 out of 14827067 pages
DETAIL:  1671 dead row versions cannot be removed yet.
There were 378926914 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 1149.21s/12598.17u sec elapsed 35893.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_38269400"
INFO:  scanned index "pg_toast_38269400_index" to remove 178956680 row versions
DETAIL:  CPU 33.85s/139.43u sec elapsed 774.95 sec
INFO:  "pg_toast_38269400": removed 178956680 row versions in 47342563 pages
DETAIL:  CPU 1267.31s/752.22u sec elapsed 22404.29 sec
INFO:  scanned index "pg_toast_38269400_index" to remove 162873580 row versions
DETAIL:  CPU 20.65s/43.54u sec elapsed 216.38 sec
INFO:  "pg_toast_38269400": removed 162873580 row versions in 39900140 pages
DETAIL:  CPU 1085.52s/716.33u sec elapsed 13775.48 sec
INFO:  index "pg_toast_38269400_index" now contains 91453965 row versions in 1622691 pages
DETAIL:  341830260 index row versions were removed.
540140 index pages have been deleted, 1626 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  "pg_toast_38269400": found 275718152 removable, 85526893 nonremovable row versions in 102611808 out of 104048880 pages
DETAIL:  1031 dead row versions cannot be removed yet.
There were 14286891 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 4786.16s/3240.77u sec elapsed 79646.66 sec.
INFO:  analyzing "public.web_pages"
INFO:  "web_pages": scanned 90000 of 14840002 pages, containing 166193 live rows and 1769 dead rows; 90000 rows in sample, 27403383 estimated total rows
VACUUM
webarchive=#

Relatório de tamanho antes:

webarchive=# \d+
                                List of relations
 Schema |                Name      |   Type   |    Owner    |    Size    | Description
--------+--------------------------+----------+-------------+------------+-------------
..... 
public | web_pages                | table    | webarchuser | 920 GB     |
.....

Depois:

webarchive=# \d+
                                List of relations
 Schema |                Name      |   Type   |    Owner    |    Size    | Description
--------+--------------------------+----------+-------------+------------+-------------
 ........
 public | web_pages                | table    | webarchuser | 920 GB     |
 ........

Eu percebo que a solução "correta" aqui seria um disco maior, mas este é um projeto de hobby (embora em uma escala muito grande), e eu simplesmente não tenho dinheiro para um armazenamento SSD maior.

Eu tenho um projeto onde estou tentando usar a ON CONFLICT DO UPDATEcláusula PostgreSQL e estou tendo um grande número de problemas de bloqueio.

Meu esquema é o seguinte:

webarchive=# \d web_pages
                                               Table "public.web_pages"
      Column       |            Type             |                              Modifiers
-------------------+-----------------------------+---------------------------------------------------------------------
 id                | integer                     | not null default nextval('web_pages_id_seq'::regclass)
 state             | dlstate_enum                | not null
 errno             | integer                     |
 url               | text                        | not null
 starturl          | text                        | not null
 netloc            | text                        | not null
 file              | integer                     |
 priority          | integer                     | not null
 distance          | integer                     | not null
 is_text           | boolean                     |
 limit_netloc      | boolean                     |
 title             | citext                      |
 mimetype          | text                        |
 type              | itemtype_enum               |
 content           | text                        |
 fetchtime         | timestamp without time zone |
 addtime           | timestamp without time zone |
 tsv_content       | tsvector                    |
 normal_fetch_mode | boolean                     | default true
 ignoreuntiltime   | timestamp without time zone | not null default '1970-01-01 00:00:00'::timestamp without time zone
Indexes:
    "web_pages_pkey" PRIMARY KEY, btree (id)
    "ix_web_pages_url" UNIQUE, btree (url)
    "idx_web_pages_title" gin (to_tsvector('english'::regconfig, title::text))
    "ix_web_pages_distance" btree (distance)
    "ix_web_pages_distance_filtered" btree (priority) WHERE state = 'new'::dlstate_enum AND distance < 1000000 AND normal_fetch_mode = true
    "ix_web_pages_id" btree (id)
    "ix_web_pages_netloc" btree (netloc)
    "ix_web_pages_priority" btree (priority)
    "ix_web_pages_state" btree (state)
    "ix_web_pages_url_ops" btree (url text_pattern_ops)
    "web_pages_state_netloc_idx" btree (state, netloc)
Foreign-key constraints:
    "web_pages_file_fkey" FOREIGN KEY (file) REFERENCES web_files(id)
Triggers:
    update_row_count_trigger BEFORE INSERT OR UPDATE ON web_pages FOR EACH ROW EXECUTE PROCEDURE web_pages_content_update_func()

Meu comando de atualização é o seguinte:

INSERT INTO
    web_pages
    (url, starturl, netloc, distance, is_text, priority, type, fetchtime, state)
VALUES
    (:url, :starturl, :netloc, :distance, :is_text, :priority, :type, :fetchtime, :state)
ON CONFLICT (url) DO
    UPDATE
        SET
            state     = EXCLUDED.state,
            starturl  = EXCLUDED.starturl,
            netloc    = EXCLUDED.netloc,
            is_text   = EXCLUDED.is_text,
            distance  = EXCLUDED.distance,
            priority  = EXCLUDED.priority,
            fetchtime = EXCLUDED.fetchtime
        WHERE
            web_pages.fetchtime < :threshtime
        AND
            web_pages.url = EXCLUDED.url
    ;

(Observação: os parâmetros são escapados por meio do SQLAlchemyestilo de consulta parametrizada)

Estou vendo dezenas de erros de deadlock, mesmo sob simultaneidade relativamente leve (6 trabalhadores):

Main.SiteArchiver.Process-5.MainThread - WARNING - SQLAlchemy OperationalError - Retrying.
Traceback (most recent call last):
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
psycopg2.extensions.TransactionRollbackError: deadlock detected
DETAIL:  Process 11391 waits for ShareLock on transaction 40632808; blocked by process 11389.
Process 11389 waits for ShareLock on transaction 40632662; blocked by process 11391.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (743427,2) in relation "web_pages"


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/media/Storage/Scripts/ReadableWebProxy/WebMirror/Engine.py", line 558, in upsertResponseLinks
    self.db_sess.execute(cmd, params=new)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1034, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/media/Storage/Scripts/ReadableWebProxy/flask/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.extensions.TransactionRollbackError) deadlock detected
DETAIL:  Process 11391 waits for ShareLock on transaction 40632808; blocked by process 11389.
Process 11389 waits for ShareLock on transaction 40632662; blocked by process 11391.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (743427,2) in relation "web_pages"
 [SQL: '         INSERT INTO          web_pages          (url, starturl, netloc, distance, is_text, priority, type, fetchtime, state)         VALUES          (%(url)s, %(starturl)s, %(netloc)s, %(distance)s, %(is_text)s, %(priority)s, %(type)s, %(fetchtime)s, %(state)s)         ON CONFLICT (url) DO          UPDATE           SET            state     = EXCLUDED.state,            starturl  = EXCLUDED.starturl,            netloc    = EXCLUDED.netloc,            is_text   = EXCLUDED.is_text,            distance  = EXCLUDED.distance,            priority  = EXCLUDED.priority,            fetchtime = EXCLUDED.fetchtime           WHERE            web_pages.fetchtime < %(threshtime)s          ;         '] [parameters: {'url': 'xxxxxx', 'is_text': True, 'netloc': 'xxxxxx', 'distance': 1000000, 'priority': 10000, 'threshtime': datetime.datetime(2016, 4, 24, 0, 38, 10, 778866), 'state': 'new', 'starturl': 'xxxxxxx', 'type': 'unknown', 'fetchtime': datetime.datetime(2016, 4, 24, 0, 38, 10, 778934)}]

Meu nível de isolamento de transação é REPEATABLE READ, então meu entendimento de como o banco de dados deve funcionar é que eu veria muitos erros de serialização, mas impasses não deveriam ocorrer porque se duas transações alterassem a mesma linha, a transação posterior simplesmente falharia.

Meu palpite aqui é que o UPDATE está de alguma forma bloqueando a consulta INSERT (ou algo assim), e preciso colocar um ponto de sincronização (?) Em algum lugar, mas não entendo o escopo dos vários componentes da consulta bem o suficiente para faça qualquer solução de problemas além de apenas mudar as coisas aleatoriamente e ver o efeito que isso tem. Eu fiz algumas leituras, mas a documentação do PostgreSQL é extremamente abstrata e a ON CONFLICT xxxterminologia ainda não parece ser amplamente usada, portanto, não há muitos recursos para solução de problemas práticos, principalmente para não especialistas em SQL.

Como posso tentar resolver esse problema? Também experimentei outros níveis de isolamento ( READ COMMITTED, SERIALIZABLE) sem sucesso.

Estou tentando obter o recurso de pesquisa de texto completo do postgres funcional.

Tenho duas tabelas, uma que criei só para teste, e a real que quero poder pesquisar:

Tabela de teste:

webarchive=# \d test_sites
                            Table "public.test_sites"
   Column    |   Type   |                        Modifiers
-------------+----------+---------------------------------------------------------
 id          | integer  | not null default nextval('test_sites_id_seq'::regclass)
 content     | text     |
 tsv_content | tsvector |
Indexes:
    "test_sites_pkey" PRIMARY KEY, btree (id)
    "idx_test_web_pages_content" gin (tsv_content)
Triggers:
    web_pages_testing_content_change_trigger AFTER INSERT OR UPDATE ON test_sites FOR EACH ROW EXECUTE PROCEDURE web_pages_testing_content_update_func()

Tabela "Real":

webarchive=# \d web_pages
                                      Table "public.web_pages"
    Column    |            Type             |                       Modifiers
--------------+-----------------------------+--------------------------------------------------------
 id           | integer                     | not null default nextval('web_pages_id_seq'::regclass)
 state        | dlstate_enum                | not null
 errno        | integer                     |
 url          | text                        | not null
 starturl     | text                        | not null
 netloc       | text                        | not null
 file         | integer                     |
 priority     | integer                     | not null
 distance     | integer                     | not null
 is_text      | boolean                     |
 limit_netloc | boolean                     |
 title        | citext                      |
 mimetype     | text                        |
 type         | itemtype_enum               |
 raw_content  | text                        |
 content      | text                        |
 fetchtime    | timestamp without time zone |
 addtime      | timestamp without time zone |
 tsv_content  | tsvector                    |
Indexes:
    "web_pages_pkey" PRIMARY KEY, btree (id)
    "ix_web_pages_url" UNIQUE, btree (url)
    "idx_web_pages_content" gin (tsv_content)
    "idx_web_pages_title" gin (to_tsvector('english'::regconfig, title::text))
    "ix_web_pages_distance" btree (distance)
    "ix_web_pages_distance_filtered" btree (priority) WHERE state = 'new'::dlstate_enum AND distance < 1000000
    "ix_web_pages_priority" btree (priority)
    "ix_web_pages_type" btree (type)
    "ix_web_pages_url_ops" btree (url text_pattern_ops)
Foreign-key constraints:
    "web_pages_file_fkey" FOREIGN KEY (file) REFERENCES web_files(id)
Triggers:
    web_pages_content_change_trigger AFTER INSERT OR UPDATE ON web_pages FOR EACH ROW EXECUTE PROCEDURE web_pages_content_update_func()

Bits extras à parte, ambos têm uma contentcoluna e uma tsv_contentcoluna com um gin()índice. Existe um gatilho que atualiza a tsv_contentcoluna toda vez que a contentcoluna é modificada.

Observe que o outro gin índice funciona bem e, na verdade, inicialmente também tinha um gin (to_tsvector('english'::regconfig, content::text))índice na coluna de conteúdo, em vez da segunda coluna, mas depois de esperar que esse índice fosse reconstruído algumas vezes nos testes, decidi usar uma coluna separada para pré-armazene os valores do tsvector.

A execução de uma consulta na tabela de teste usa o índice como eu esperaria:

webarchive=# EXPLAIN ANALYZE SELECT
    test_sites.id,
    test_sites.content,
    ts_rank_cd(test_sites.tsv_content, to_tsquery($$testing$$)) AS ts_rank_cd_1
FROM
    test_sites
WHERE
    test_sites.tsv_content @@ to_tsquery($$testing$$);
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_sites  (cost=16.45..114.96 rows=25 width=669) (actual time=0.175..3.720 rows=143 loops=1)
   Recheck Cond: (tsv_content @@ to_tsquery('testing'::text))
   Heap Blocks: exact=117
   ->  Bitmap Index Scan on idx_test_web_pages_content  (cost=0.00..16.44 rows=25 width=0) (actual time=0.109..0.109 rows=143 loops=1)
         Index Cond: (tsv_content @@ to_tsquery('testing'::text))
 Planning time: 0.414 ms
 Execution time: 3.800 ms
(7 rows)

No entanto, a mesma consulta exata na tabela real nunca parece resultar em nada além de uma simples varredura sequencial:

webarchive=# EXPLAIN ANALYZE SELECT
       web_pages.id,
       web_pages.content,
       ts_rank_cd(web_pages.tsv_content, to_tsquery($$testing$$)) AS ts_rank_cd_1
   FROM
       web_pages
   WHERE
       web_pages.tsv_content @@ to_tsquery($$testing$$);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on web_pages  (cost=0.00..4406819.80 rows=19751 width=505) (actual time=0.343..142325.954 rows=134949 loops=1)
   Filter: (tsv_content @@ to_tsquery('testing'::text))
   Rows Removed by Filter: 12764373
 Planning time: 0.436 ms
 Execution time: 142341.489 ms
(5 rows)

Aumentei minha memória de trabalho para 3 GB para ver se esse era o problema, e não é.

Além disso, deve-se observar que essas tabelas são bastante grandes - ~ 150 GB de texto em 4 milhões de linhas (com 8 milhões de linhas adicionais em que content/ tsv_contenté NULL).

A test_sitestabela tem 1/1000 das linhas de web_pages, pois é um pouco proibitivo experimentar quando cada consulta leva vários minutos.

Estou usando o postgresql 9.5 (sim, eu mesmo compilei, queria ON CONFLICT). Não parece haver uma etiqueta para isso ainda.

Eu li os problemas em aberto com o 9.5 e não consigo ver isso como resultado de nenhum deles.

Recém-saído de uma reconstrução completa do índice, o problema ainda existe:

webarchive=# ANALYZE web_pages ;
ANALYZE
webarchive=# EXPLAIN ANALYZE SELECT
    web_pages.id,
    web_pages.content,
    ts_rank_cd(web_pages.tsv_content, to_tsquery($$testing$$)) AS ts_rank_cd_1
FROM
    web_pages
WHERE
    web_pages.tsv_content @@ to_tsquery($$testing$$);
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on web_pages  (cost=10000000000.00..10005252343.30 rows=25109 width=561) (actual time=7.114..146444.168 rows=134949 loops=1)
   Filter: (tsv_content @@ to_tsquery('testing'::text))
   Rows Removed by Filter: 13137318
 Planning time: 0.521 ms
 Execution time: 146465.188 ms
(5 rows)

Observe que eu literalmente acabei ANALYZEde editar e o seqscan está desativado.

Sim, mais perguntas do tipo n-por-grupo.

Dada a uma tabela releasescom as seguintes colunas:

 id         | primary key                 | 
 volume     | double precision            |
 chapter    | double precision            |
 series     | integer-foreign-key         |
 include    | boolean                     | not null

Quero selecionar o máximo composto de volume e, em seguida, capítulo para um conjunto de séries.

No momento, se eu consultar por séries distintas, posso fazer isso facilmente da seguinte maneira:

SELECT 
       releases.chapter AS releases_chapter,
       releases.include AS releases_include,
       releases.series AS releases_series
FROM releases
WHERE releases.series = 741
  AND releases.include = TRUE
ORDER BY releases.volume DESC NULLS LAST, releases.chapter DESC NULLS LAST LIMIT 1;

No entanto, se eu tiver um grande conjunto de series(e tenho), isso rapidamente se depara com problemas de eficiência em que estou emitindo mais de 100 consultas para gerar uma única página.

Eu gostaria de colocar tudo em uma única consulta, onde posso simplesmente dizer WHERE releases.series IN (1,2,3....), mas não descobri como convencer o Postgres a me deixar fazer isso.

A abordagem ingênua seria:

SELECT releases.volume AS releases_volume,
       releases.chapter AS releases_chapter,
       releases.series AS releases_series
FROM 
    releases
WHERE 
    releases.series IN (12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499, 
                        556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137, 
                        1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768, 
                        1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255, 
                        2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606, 
                        2634, 2636, 2695, 2696 )
  AND releases.include = TRUE
GROUP BY 
    releases_series
ORDER BY releases.volume DESC NULLS LAST, releases.chapter DESC NULLS LAST;

O que obviamente não funciona:

ERROR:  column "releases.volume" must appear in the 
        GROUP BY clause or be used in an aggregate function

Sem o GROUP BY, ele busca tudo, e com alguma filtragem processual simples até funcionaria, mas deve haver uma maneira "adequada" de fazer isso no SQL.

Seguindo os erros e adicionando agregados:

SELECT max(releases.volume) AS releases_volume,
       max(releases.chapter) AS releases_chapter,
       releases.series AS releases_series
FROM 
    releases
WHERE 
    releases.series IN (12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499, 
                        556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137, 
                        1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768, 
                        1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255, 
                        2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606, 
                        2634, 2636, 2695, 2696 )
  AND releases.include = TRUE
GROUP BY 
    releases_series;

Na maioria das vezes funciona, mas o problema é que os dois máximos não são coerentes. Se eu tiver duas linhas, uma em que volume:capítulo é 1:5 e 4:1, preciso retornar 4:1, mas os máximos independentes retornam 4:5.

Francamente, isso seria tão simples de implementar no código do meu aplicativo que devo estar perdendo algo óbvio aqui. Como posso implementar uma consulta que realmente satisfaça meus requisitos?

Eu tenho uma consulta de exclusão bastante simples em um banco de dados PostgreSQL com o qual estou interagindo via psycopg2.

Tome o seguinte exemplo mínimo:

def testDelete():
    db = DbInterface()
    cur = db.conn.cursor()
    cur.execute("DELETE FROM munamelist WHERE name='something'")
    print("Results = ", cur.fetchall())

Basicamente, a documentação do PostgreSQL paraDELETE estados:

Após a conclusão bem-sucedida, um comando DELETE retorna uma tag de comando no formato

DELETE count

A contagem é o número de linhas excluídas. Observe que o número pode ser menor que o número de linhas que corresponderam à condição quando as exclusões foram suprimidas por um gatilho BEFORE DELETE. Se count for 0, nenhuma linha foi excluída pela consulta (isso não é considerado um erro).

No entanto, psycopg2gera um erro ao tentar buscar os resultados da consulta:

Traceback (most recent call last):
  File "autoOrganize.py", line 370, in <module>
    parseCommandLine()
  File "autoOrganize.py", line 363, in parseCommandLine
    testDelete()
  File "autoOrganize.py", line 247, in testDelete
    print("Results = ", cur.fetchall())
psycopg2.ProgrammingError: no results to fetch

Não importa se o item existe ou não, você não pode buscar os resultados da consulta. Não psycopg2retorna "tags de comando" para SQL?

Caso contrário, como posso recuperar o número de linhas alteradas que seriam retornadas na interface do console? Não importa, aparentemente cursor.rowcounté o número de linhas modificadas na última instrução DML/ DQL.

Estou trabalhando com o PostgreSQL 9.3 usando a psycopg2API do banco de dados.

Eu tenho a API do banco de dados definida no nível mínimo de isolamento (modo "autocommit") e estou gerenciando minhas próprias transações diretamente via SQL. Exemplo:

cur = self.conn.cursor()
cur.execute("BEGIN;")
cur.execute("SELECT dbId, downloadPath, fileName, tags FROM {tableName} WHERE dlState=%s".format(tableName=self.tableName), (2, ))
ret = cur.fetchall()
cur.execute("COMMIT;")

Basicamente, a transação iniciada pelo cur.execute("BEGIN;")limita-se apenas a esse cursor ou é para toda a conexão ( self.conn.cursor())?

Algumas das coisas mais complexas que estou fazendo envolvem várias operações de banco de dados separadas, que logicamente divido em funções. Como tudo isso está em uma classe que tem a conexão como membro, é muito mais conveniente criar cursores dentro de cada função. No entanto, não tenho certeza de como funciona a criação de cursores em uma transação.

Basicamente, se as transações forem por conexão, posso criar muitos cursores instantaneamente dentro da transação. Se eles forem por cursor, isso significa que tenho que passar o cursor por todos os lugares. Qual é?

A documentação não aborda isso, embora o fato de você poder ligar connection.commit()me deixe bastante confiante de que o controle da transação é por conexão.

Como posso GROUP BYuma coluna, enquanto ordenando apenas por outra.

Estou tentando fazer o seguinte:

SELECT dbId,retreivalTime 
    FROM FileItems 
    WHERE sourceSite='something' 
    GROUP BY seriesName 
    ORDER BY retreivalTime DESC 
    LIMIT 100 
    OFFSET 0;

Quero selecionar os últimos /n/ itens de FileItems, em ordem decrescente, com as linhas filtradas por DISTINCTvalores de seriesName. A consulta acima apresenta erros ERROR: column "fileitems.dbid" must appear in the GROUP BY clause or be used in an aggregate function. Eu preciso do dbidvalor para então pegar a saída dessa consulta e JOINna tabela de origem para pegar o restante das colunas que eu estava.

Observe que esta é basicamente a gestalt da pergunta abaixo, com muitos detalhes irrelevantes removidos para maior clareza.

Pergunta original

Eu tenho um sistema que estou migrando do sqlite3 para o PostgreSQL, porque superei em grande parte o sqlite:

    SELECT
            d.dbId,
            d.dlState,
            d.sourceSite,
        [snip a bunch of rows]
            d.note

    FROM FileItems AS d
        JOIN
            ( SELECT dbId
                FROM FileItems
                WHERE sourceSite='{something}'
                GROUP BY seriesName
                ORDER BY MAX(retreivalTime) DESC
                LIMIT 100
                OFFSET 0
            ) AS di
            ON  di.dbId = d.dbId
    ORDER BY d.retreivalTime DESC;

Basicamente, quero selecionar os últimos n DISTINCTitens no banco de dados, onde a restrição distinta está em uma coluna e a ordem de classificação está em uma coluna diferente.

Infelizmente, a consulta acima, embora funcione bem no sqlite, apresenta erros no PostgreSQL com o erro psycopg2.ProgrammingError: column "fileitems.dbid" must appear in the GROUP BY clause or be used in an aggregate function.

Infelizmente, enquanto adicionar dbIdà cláusula GROUP BY corrige o problema (por exemplo GROUP BY seriesName,dbId, ), isso significa que a filtragem distinta nos resultados da consulta não funciona mais, pois dbidé a chave primária do banco de dados e, como tal, todos os valores são distintos.

Da leitura da documentação do Postgres , existe SELECT DISTINCT ON ({nnn}), mas isso requer que os resultados retornados sejam classificados por {nnn}.

Portanto, para fazer o que eu quero via SELECT DISTINCT ON, eu teria que consultar todos DISTINCT {nnn}e seus MAX(retreivalTime), classificar novamente em retreivalTimevez de {nnn}, então pegar o maior 100 e consultar usando-os na tabela para obter o restante das linhas, que eu gostaria de evitar, como o banco de dados tem ~ 175 mil linhas e ~ 14 mil valores distintos na seriesNamecoluna, eu só quero os 100 mais recentes e essa consulta é um pouco crítica para o desempenho (preciso de tempos de consulta < 1/2 segundo).

Minha suposição ingênua aqui é basicamente que o banco de dados precisa apenas iterar sobre cada linha em ordem decrescente de retreivalTime, e simplesmente parar depois de ver os LIMITitens, portanto, uma consulta de tabela completa não é ideal, mas não pretendo realmente entender como o banco de dados sistema otimiza internamente, e eu posso estar abordando isso completamente errado.

FWIW, ocasionalmente uso OFFSETvalores diferentes, mas longos tempos de consulta para casos em que deslocamento > ~ 500 é completamente aceitável. Basicamente, OFFSETé um mecanismo de paginação de baixa qualidade que me permite fugir sem precisar dedicar cursores de rolagem a cada conexão, e provavelmente o revisitarei em algum momento.

Ref- Pergunta que fiz há um mês que levou a esta consulta .

Bom, mais notas:

    SELECT
            d.dbId,
            d.dlState,
            d.sourceSite,
        [snip a bunch of rows]
            d.note

    FROM FileItems AS d
        JOIN
            ( SELECT seriesName, MAX(retreivalTime) AS max_retreivalTime
                FROM FileItems
                WHERE sourceSite='{something}'
                GROUP BY seriesName
                ORDER BY max_retreivalTime DESC
                LIMIT %s
                OFFSET %s
            ) AS di
            ON  di.seriesName = d.seriesName AND di.max_retreivalTime = d.retreivalTime
    ORDER BY d.retreivalTime DESC;

Funciona corretamente para a consulta conforme descrito, mas se eu remover a GROUP BYcláusula, ela falha (é opcional no meu aplicativo).

psycopg2.ProgrammingError: column "FileItems.seriesname" must appear in the GROUP BY clause or be used in an aggregate function

Acho que fundamentalmente não estou entendendo como as subconsultas funcionam no PostgreSQL. Onde eu estou errando? Eu tinha a impressão de que uma subconsulta é basicamente apenas uma função embutida, onde os resultados são apenas alimentados na consulta principal.

Eu tenho um pequeno aplicativo da web que está usando sqlite3 como banco de dados (o banco de dados é bem pequeno).

No momento, estou gerando algum conteúdo para exibir usando a seguinte consulta:

SELECT dbId,
        dlState,
        retreivalTime,
        seriesName,
        <snip irrelevant columns>
        FROM DataItems
        GROUP BY seriesName
        ORDER BY retreivalTime DESC
        LIMIT ?
        OFFSET ?;

Onde limitnormalmente é ~200 e offseté 0 (eles acionam um mecanismo de paginação).

De qualquer forma, agora, essa consulta está acabando completamente com meu desempenho. Leva aproximadamente 800 milissegundos para executar em uma tabela com aproximadamente 67 mil linhas.

Eu tenho índices em ambos seriesNamee retreivalTime.

sqlite> SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;
<snip irrelevant indexes>
DataItems_seriesName_index
DataItems_time_index           // This is the index on retreivalTime. Yeah, it's poorly named

No entanto, EXPLAIN QUERY PLANparece indicar que eles não estão sendo usados:

sqlite> EXPLAIN QUERY PLAN SELECT dbId, 
                                  dlState, 
                                  retreivalTime, 
                                  seriesName 
                                  FROM 
                                      DataItems 
                                  GROUP BY 
                                      seriesName 
                                  ORDER BY 
                                      retreivalTime 
                                  DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY

O índice seriesNameé COLLATE NOCASE, se isso for relevante.

Se eu soltar o GROUP BY, ele se comportará conforme o esperado:

sqlite> EXPLAIN QUERY PLAN SELECT dbId, dlState, retreivalTime, seriesName FROM DataItems ORDER BY retreivalTime DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems USING INDEX DataItems_time_index

Basicamente, minha suposição ingênua seria que a melhor maneira de executar essa consulta seria voltar do último valor em retreivalTime, e toda vez que um novo valor seriesNamefor visto, anexá-lo a uma lista temporária e, finalmente, retornar esse valor. Isso teria um desempenho um tanto ruim para casos em que OFFSETé grande, mas isso acontece muito raramente neste aplicativo.

Como posso otimizar esta consulta? Posso fornecer as operações de consulta bruta, se necessário.

O desempenho de inserção não é crítico aqui, portanto, se eu precisar criar um ou dois índices adicionais, tudo bem.

Meus pensamentos atuais são um gancho de confirmação que atualiza uma tabela separada usada para rastrear apenas itens exclusivos, mas isso parece um exagero.

Eu tenho um grande banco de dados (16 milhões de linhas) contendo hashes perceptivos de imagens.

Eu gostaria de poder procurar linhas por hamming distância em um período de tempo razoável.

Atualmente, tanto quanto eu entendo corretamente o problema, acho que a melhor opção aqui seria uma implementação SP-GiST personalizada que implementa um BK-Tree , mas isso parece muito trabalhoso e ainda estou confuso na prática detalhes da implementação adequada de um índice personalizado. Calcular a distância hamming é tratável o suficiente, e eu conheço C, no entanto.

Basicamente, qual é a abordagem apropriada aqui? Eu preciso ser capaz de consultar correspondências dentro de uma certa distância de edição de um hash. Pelo que entendi, a distância Levenshtein com strings de comprimento igual é uma distância hamming funcional, então há pelo menos algum suporte existente para o que eu quero, embora não haja uma maneira clara de criar um índice a partir dele (lembre-se, o valor que estou consultando mudanças. Não posso pré-calcular a distância a partir de um valor fixo, pois isso só seria útil para aquele valor).

Os hashes são atualmente armazenados como uma string de 64 caracteres contendo a codificação ASCII binária do hash (por exemplo, "10010101..."), mas posso convertê-los para int64 com bastante facilidade. O problema real é que preciso ser capaz de consultar relativamente rápido.

Parece que seria possível conseguir algo na linha do que eu quero com o pg_trgm, mas estou um pouco confuso sobre como funciona o mecanismo de correspondência de trigramas (em particular, o que a métrica de similaridade que ele retorna realmente representa? Parece tipo distância de edição).

O desempenho de inserção não é crítico (é muito caro computacionalmente calcular os hashes para cada linha), então eu me preocupo principalmente com a pesquisa.