Re: 8.1 count(*) distinct: IndexScan/SeqScan

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: 8.1 count(*) distinct: IndexScan/SeqScan
Дата
Msg-id 17380.1132886273@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: 8.1 count(*) distinct: IndexScan/SeqScan  (Pailloncy Jean-Gerard <jg@rilk.com>)
Ответы Re: 8.1 count(*) distinct: IndexScan/SeqScan  (Kyle Cordes <kyle@kylecordes.com>)
Re: 8.1 count(*) distinct: IndexScan/SeqScan  (Pailloncy Jean-Gerard <jg@rilk.com>)
Список pgsql-performance
Pailloncy Jean-Gerard <jg@rilk.com> writes:
> I redo the test, with a freshly installed data directory. Same result.

What "same result"?  You only ran it up to 2K rows, not 2M.  In any
case, EXPLAIN without ANALYZE is pretty poor ammunition for complaining
that the planner made the wrong choice.  I ran the same test case,
and AFAICS the indexscan is the right choice at 2K rows:

regression=# explain analyze select count(*) from (select distinct on (val) *   from test) as foo;
                                                            QUERY PLAN
          

----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=105.24..105.25 rows=1 width=0) (actual time=41.561..41.565 rows=1 loops=1)
   ->  Unique  (cost=0.00..79.63 rows=2048 width=8) (actual time=0.059..32.459 rows=2048 loops=1)
         ->  Index Scan using testval on test  (cost=0.00..74.51 rows=2048 width=8) (actual time=0.049..13.197
rows=2048loops=1) 
 Total runtime: 41.683 ms
(4 rows)

regression=# set enable_indexscan TO 0;
SET
regression=# explain analyze select count(*) from (select distinct on (val) *   from test) as foo;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=179.96..179.97 rows=1 width=0) (actual time=59.567..59.571 rows=1 loops=1)
   ->  Unique  (cost=144.12..154.36 rows=2048 width=8) (actual time=21.438..50.434 rows=2048 loops=1)
         ->  Sort  (cost=144.12..149.24 rows=2048 width=8) (actual time=21.425..30.589 rows=2048 loops=1)
               Sort Key: test.val
               ->  Seq Scan on test  (cost=0.00..31.48 rows=2048 width=8) (actual time=0.014..9.902 rows=2048 loops=1)
 Total runtime: 60.265 ms
(6 rows)


            regards, tom lane

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

Предыдущее
От: Pailloncy Jean-Gerard
Дата:
Сообщение: Re: 8.1 count(*) distinct: IndexScan/SeqScan
Следующее
От: Kyle Cordes
Дата:
Сообщение: Re: 8.1 count(*) distinct: IndexScan/SeqScan