优化PostgreSQL的查询与ORDER BY，左连接和限制

  • Z时代
  • 2024-01-10
  • 分类：问答

林”寻求帮助，因为我无法找到优化这个查询的好办法：现在优化PostgreSQL的查询与ORDER BY，左连接和限制

SELECT 
b.book_id, 
b.asin, 
b.type_book 
FROM book b 
LEFT JOIN product_maj_plateforme pmp 
     ON pmp.book_id_fk = b.book_id AND pmp.plateforme_id_fk = 1 
WHERE 
deleted = 0 
AND (
     pmp.book_id_fk IS NULL OR (
     pmp.book_id_fk IS NOT NULL 
     AND pmp.date_updated < now() - INTERVAL '1 SECOND' * b.ttl 
)) 
AND asin IS NOT NULL 
AND asin != '' 
AND asin != '0' 
AND price_achat > 0 
ORDER BY b.ttl asc 
LIMIT 400;

，这里是我的执行计划：

'Limit (cost=0.00..8702.51 rows=400 width=20) (actual time=3284.647..3287.175 rows=400 loops=1)' 
' Output: b.book_id, b.asin, b.type_book, b.ttl' 
' -> Nested Loop Left Join (cost=0.00..4499522.06 rows=206815 width=20) (actual time=3284.647..3287.128 rows=400 loops=1)' 
'  Output: b.book_id, b.asin, b.type_book, b.ttl' 
'  Filter: ((pmp.book_id_fk IS NULL) OR ((pmp.book_id_fk IS NOT NULL) AND (pmp.date_updated < (now() - ('00:00:01'::interval * (b.ttl)::double precision)))))' 
'  Rows Removed by Filter: 631559' 
'  -> Index Scan using book_ix_test on public.book l (cost=0.00..324455.62 rows=620444 width=20) (actual time=0.014..893.522 rows=631959 loops=1)' 
'    Output: b.book_id, b.code, b.date_created, b.date_updated, b.titre, b.deleted, b.encours, b.date_encours, b.prix_achat, b.prix_vente, b.marchand_id_achat, b.etat_id_achat, b.poids, b.prix_vente_max, b.prix_vente_conc, b.asin, b.item_condition, b.type_book, b.author, b.edition, b.quantity, b.ttl' 
'    Filter: ((b.asin IS NOT NULL) AND ((b.asin)::text <> ''::text) AND ((b.asin)::text <> '0'::text) AND (b.prix_achat > 0::double precision) AND (b.deleted = 0) AND (b.encours = 0))' 
'    Rows Removed by Filter: 441235' 
'  -> Index Scan using produit_maj_plateforme_uniq on public.produit_maj_plateforme pmp (cost=0.00..6.71 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=631959)' 
'    Output: pmp.book_id_fk, pmp.date_updated' 
'    Index Cond: ((pmp.book_id_fk = b.book_id) AND (pmp.plateforme_id_fk = 1))' 
'Total runtime: 3287.333 ms'

我认为这个问题是嵌套循环左连接，但我不能找到一种方法来避免它。

这个查询需要大约10秒至事先我专用的32Gb + SSD服务器上执行

感谢

编辑：

PostgreSQL的版本：9.3（在Debian）

表书： 〜1200000行

CREATE TABLE book 
(
    book_id serial NOT NULL, 
    code character varying(255) NOT NULL, 
    date_created timestamp without time zone NOT NULL, 
    date_updated timestamp without time zone, 
    titre character varying(1000), 
    deleted smallint DEFAULT 0, 
    encours smallint NOT NULL DEFAULT 0, 
    date_encours timestamp without time zone, 
    price_achat double precision NOT NULL DEFAULT 0, 
    price_vente double precision NOT NULL DEFAULT 0, 
    marchand_id_achat integer, 
    etat_id_achat integer, 
    poids double precision, 
    price_vente_max double precision NOT NULL DEFAULT 0, 
    price_vente_conc double precision NOT NULL DEFAULT 0, 
    asin character varying(255), 
    item_condition smallint, 
    type_livre smallint NOT NULL DEFAULT 0, 
    author character varying(250), 
    edition character varying(250), 
    quantity smallint NOT NULL DEFAULT 1, 
    ttl integer DEFAULT 64800, 
    CONSTRAINT livre_pkey PRIMARY KEY (livre_id) 
); 
CREATE INDEX book_ix_get_to_be_checked_2 
    ON book 
    USING btree 
    (type_livre DESC, ttl); 
CREATE INDEX book_ix_test 
    ON book 
    USING btree 
    (ttl);

表：〜1200 000线×6

CREATE TABLE product_maj_plateforme 
(
    product_maj_plateforme_id serial NOT NULL, 
    book_id_fk integer NOT NULL, 
    plateforme_id_fk integer NOT NULL, 
    date_updated timestamp without time zone NOT NULL, 
    CONSTRAINT produit_maj_plateforme_pkey PRIMARY KEY (produit_maj_plateforme_id), 
    CONSTRAINT book_id_fk_key FOREIGN KEY (book_id_fk) 
     REFERENCES book (book_id) MATCH SIMPLE 
     ON UPDATE NO ACTION ON DELETE RESTRICT, 
    CONSTRAINT plateforme_id_fk_key FOREIGN KEY (plateforme_id_fk) 
     REFERENCES plateforme (plateforme_id) MATCH SIMPLE 
     ON UPDATE NO ACTION ON DELETE RESTRICT 
) 
CREATE UNIQUE INDEX produit_maj_plateforme_uniq 
    ON produit_maj_plateforme 
    USING btree 
    (livre_id_fk, plateforme_id_fk);

不用担心拼错专栏中，我试图从法语到英语到traduct为了更好的理解，但它可能会错过一些改变

回答：

试着这么做：

SELECT 
b.book_id, 
b.asin, 
b.type_book 
FROM book b 
WHERE deleted = 0   
AND asin != '' 
AND asin != '0' 
AND price_achat > 0 
AND NOT EXISTS (SELECT 1 
       FROM product_maj_plateforme pmp 
       WHERE pmp.book_id_fk = b.book_id AND pmp.plateforme_id_fk = 1 
        AND pmp.date_updated > now() - INTERVAL '1 SECOND' * b.ttl) 
ORDER BY b.ttl asc 
LIMIT 400;

稍微改变查询的逻辑，但可能适合你和更快。

加快查询另一种方式是在(ttl, deleted, asin)或(ttl, deleted, price_achat)

回答：

假设删除，ASIN和price_achat是部分你的书桌上。 这是更快吗？

SELECT 
b.book_id, 
b.asin, 
b.type_book 
FROM (select * from book where deleted = 0 and asin IS NOT NULL AND asin != '' AND asin != '0' AND price_achat > 0) b 
LEFT JOIN product_maj_plateforme pmp 
     ON pmp.book_id_fk = b.book_id AND pmp.plateforme_id_fk = 1 
WHERE 
     pmp.book_id_fk IS NULL OR 
     (
      pmp.book_id_fk IS NOT NULL 
      AND pmp.date_updated < now() - INTERVAL '1 SECOND' * b.ttl 
     ) 
ORDER BY b.ttl asc 
LIMIT 400;

以上是 优化PostgreSQL的查询与ORDER BY，左连接和限制 的全部内容， 来源链接： utcz.com/qa/265701.html

回到顶部