Início / dba / Perguntas / 321404
Accepted
sirajalam049
Asked: 2022-12-25 04:36:50 +0800 CST

Melhor maneira de SELECT em uma tabela grande com filtro para filtrar cerca de 1 milhão de linhas com índices

  • 772

Eu tenho uma tabela billing_billcycleordersque contém uma chave estrangeira billing_cycle_id. Existem cerca de 0,9 milhão de registros para um arquivo billing_cycle_id. Desejo selecionar os dados nos blocos para ~ 5000 (ou da melhor maneira possível).

O resultado da consulta aumenta exponencialmente para valores de billing_cycle_idcom o maior número de registros.

Eu corri EXPLAIN ANALYZEna consulta

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, format text) 
SELECT DISTINCT 
    "billing_billcycleorders"."id", 
    "billing_billcycleorders"."created", 
    "billing_billcycleorders"."updated", 
    "billing_billcycleorders"."deleted", 
    "billing_billcycleorders"."deleted_date", 
    "billing_billcycleorders"."billing_cycle_id", 
    "billing_billcycleorders"."order_id", 
    "billing_billcycleorders"."pickrr_awb", 
    "billing_billcycleorders"."amount", 
    "billing_billcycleorders"."delivery_bill", 
    "billing_billcycleorders"."rto_bill", 
    "billing_billcycleorders"."pickup_bill", 
    "billing_billcycleorders"."surcharge", 
    "billing_billcycleorders"."cod_bill", 
    "billing_billcycleorders"."qc_bill", 
    "billing_billcycleorders"."qcf_bill", 
    "billing_billcycleorders"."secure_shipment_charge", 
    "billing_billcycleorders"."cod_amount", 
    "billing_billcycleorders"."meta_details" 
FROM 
    "billing_billcycleorders" 
WHERE 
    (
        "billing_billcycleorders"."billing_cycle_id" = 685081 AND "billing_billcycleorders"."id" > 0
    ) 
ORDER BY "billing_billcycleorders"."id" ASC 
LIMIT 1000

Explique Analisar: https://explain.depesz.com/s/k9rq7

postgresql

1 respostas

  1. Best Answer

    Paginar uma tabela com OFFSET/ LIMITincorre no custo de percorrer o número combinado de linhas e fica cada vez mais caro.

    Em vez disso, baseie suas páginas de dados em um índice. Você pode começar LIMIT 1000como está, mas em vez de OFFSETnas páginas subsequentes, use:

    ...
WHERE ...
AND   billing_billcycleorders.id > $greatest_id_of_last_batch  -- !
LIMIT 1000;

    $greatest_id_of_last_batchsendo o maior ID da página de dados anterior. Isso corresponde à sua ordem de classificação ORDER BY "billing_billcycleorders"."id" ASC. E idé o PK, então é único.

    Certifique-se de ter um índice de várias colunas ativado (billing_cycle_id, id)para suportar isso. Ver:

    E, claro, solte o DISTINCTno topo, isso não está fazendo nada de útil nesta consulta (exceto tornando-a cara).

    À parte: com um milhão de linhas por ciclo de cobrança, particionar a tabela por lista billing_cycle_idpode ser uma boa opção ...

    • 3

relate perguntas