can someone explain that?

Поиск
Список
Период
Сортировка
От Luis Amigo
Тема can someone explain that?
Дата
Msg-id 3C19CAF4.1FEFD6FA@atc.unican.es
обсуждение исходный текст
Ответы Re: can someone explain that?  (Martijn van Oosterhout <kleptog@svana.org>)
Список pgsql-general
while explaining this query.

We're getting this strange result:
Limit  (cost=840340.22..840340.22 rows=6 width=53) (actual
time=-3035.03..-3034.97 rows=3 loops=1)
  ->  Sort  (cost=840340.22..840340.22 rows=7 width=53) (actual
time=-3035.07..-3035.05 rows=3 loops=1)
        ->  Aggregate  (cost=840339.80..840340.13 rows=7 width=53)
(actual time=-3037.55..-3036.18 rows=3 loops=1)
              ->  Group  (cost=840339.80..840339.96 rows=67 width=53)
(actual time=-3038.21..-3036.80 rows=37 loops=1)
                    ->  Sort  (cost=840339.80..840339.80 rows=67
width=53) (actual time=-3038.26..-3038.00 rows=37 loops=1)
                          ->  Nested Loop  (cost=6.88..840337.78 rows=67
width=53) (actual time=532.35..-3040.17 rows=37 loops=1)
                                ->  Hash Join  (cost=6.88..839378.23
rows=201 width=49) (actual time=531.00..-3094.78 rows=86 loops=1)
                                      ->  Seq Scan on lineitem l1
(cost=0.00..839343.72 rows=5025 width=8) (actual time=45.33..-3151.70
rows=3079 loops=1)
                                            SubPlan
                                              ->  Index Scan using
lineitem_pkey on lineitem l2  (cost=0.00..18.33 rows=5 width=172)
(actual time=0.20..0.20 rows=1 loops=30334)
                                              ->  Index Scan using
lineitem_pkey on lineitem l3  (cost=0.00..18.34 rows=2 width=172)
(actual time=0.24..0.24 rows=1 loops=29240)
                                      ->  Hash  (cost=6.87..6.87 rows=4
width=41) (actual time=5.78..5.78 rows=0 loops=1)
                                            ->  Hash Join
(cost=1.31..6.87 rows=4 width=41) (actual time=1.46..5.71 rows=3
loops=1)
                                                  ->  Seq Scan on
supplier  (cost=0.00..5.00 rows=100 width=37) (actual time=0.06..3.24
rows=100 loops=1)
                                                  ->  Hash
(cost=1.31..1.31 rows=1 width=4) (actual time=0.61..0.61 rows=0 loops=1)

                                                        ->  Seq Scan on
nation  (cost=0.00..1.31 rows=1 width=4) (actual time=0.47..0.56 rows=1
loops=1)
                                ->  Index Scan using orders_pkey on
orders  (cost=0.00..4.76 rows=1 width=4) (actual time=0.57..0.58 rows=0
loops=86)
Total runtime: -3032.37 msec
Can someone explain this?
explain analyze
SELECT
    supplier.name,
    count(*) AS numwait
FROM
    supplier,
    lineitem l1,
    orders,
    nation
WHERE
    supplier.suppkey=l1.suppkey
    AND orders.orderkey=l1.orderkey
    AND orders.orderstatus='F'
    AND l1.receiptdate>l1.commitdate
    AND EXISTS(
        SELECT
            *
        FROM
            lineitem l2
        WHERE
            l2.orderkey=l1.orderkey
            AND l2.suppkey<>l1.suppkey
        )
    AND NOT EXISTS(
        SELECT
            *
        FROM
            lineitem l3
        WHERE
            l3.orderkey=l1.orderkey
            AND l3.suppkey<>l1.suppkey
            AND l3.receiptdate>l3.commitdate
        )
    AND supplier.nationkey=nation.nationkey
    AND nation.name='SAUDI ARABIA'
GROUP BY
    supplier.name
ORDER BY
    numwait DESC,
    supplier.name
LIMIT 100;

В списке pgsql-general по дате отправления:

Предыдущее
От: Antonio Fiol Bonnín
Дата:
Сообщение: Re: slow queries on large syslog table
Следующее
От: "omid omoomi"
Дата:
Сообщение: Re: slow queries on large syslog table