AGG_PLAIN thinks sorts are free

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема AGG_PLAIN thinks sorts are free
Дата
Msg-id CAMkU=1yRW-_Ab=tW2uX1FY0A-kEjOJWiiFtx9oAx_F2P-nwFUw@mail.gmail.com
обсуждение исходный текст
Ответы Re: AGG_PLAIN thinks sorts are free  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
AGG_PLAIN sometimes does sorts, but it thinks they are free.  Also, under explain analyze it does not explicitly report whether the sort was external or not, nor report the disk or memory usage, the way other sorts do.  I don't know if those two things are related or not.  

This behavior seems to be ancient, at least back to 8.4.

Does someone more familiar with this part of the code know if this is a simple oversight or a fundamental design issue?

Here is a test case, in which adding a "distinct" increases the run time 500% but doesn't change the estimate at all:

create table foo as select (random()*1000000)::int as val from generate_series(1,20000000);

analyze foo;


explain (analyze,buffers) select count(distinct val) from foo;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=338497.20..338497.21 rows=1 width=4) (actual time=28185.597..28185.598 rows=1 loops=1)
   Buffers: shared hit=192 read=88304, temp read=112326 written=112326
   I/O Timings: read=200.810
   ->  Seq Scan on foo  (cost=0.00..288496.96 rows=20000096 width=4) (actual time=0.040..2192.281 rows=20000000 loops=1)
         Buffers: shared hit=192 read=88304
         I/O Timings: read=200.810
 Total runtime: 28185.628 ms

explain (analyze,buffers) select count(val) from foo;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=338497.20..338497.21 rows=1 width=4) (actual time=4230.892..4230.892 rows=1 loops=1)
   Buffers: shared hit=224 read=88272
   I/O Timings: read=145.003
   ->  Seq Scan on foo  (cost=0.00..288496.96 rows=20000096 width=4) (actual time=0.098..2002.396 rows=20000000 loops=1)
         Buffers: shared hit=224 read=88272
         I/O Timings: read=145.003
 Total runtime: 4230.948 ms

Cheers,

Jeff

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Differences in WHERE clause of SELECT
Следующее
От: Greg Smith
Дата:
Сообщение: Re: [v9.4] row level security