Re: Abbreviated keys for text cost model fix
От | Jeremy Harris |
---|---|
Тема | Re: Abbreviated keys for text cost model fix |
Дата | |
Msg-id | 54ED180C.7040308@wizmail.org обсуждение исходный текст |
Ответ на | Re: Abbreviated keys for text cost model fix (Tomas Vondra <tomas.vondra@2ndquadrant.com>) |
Ответы |
Re: Abbreviated keys for text cost model fix
(Peter Geoghegan <pg@heroku.com>)
Re: Abbreviated keys for text cost model fix (Jeremy Harris <jgh@wizmail.org>) |
Список | pgsql-hackers |
On 23/02/15 16:40, Tomas Vondra wrote: > On 22.2.2015 22:30, Peter Geoghegan wrote: >> You should try it with the data fully sorted like this, but with one >> tiny difference: The very last tuple is out of order. How does that >> look? If this case is actually important, a merge-sort can take significant advantage of the partial order: test=# explain analyze select * from (select * from stuff_text_asc order by randtxt offset 100000000000) foo; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------Limit (cost=247054.81..247054.81 rows=1 width=18) (actual time=25133.029..25133.029 rows=0 loops=1) -> Sort (cost=242054.81..247054.81 rows=2000001 width=18) (actual time=25025.931..25088.406 rows=2000001 loops=1) Sort Key: stuff_text_asc.randtxt Sort Method: quicksort Memory:221213kB Compares: 95541376 -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.011..118.390 rows=2000001 loops=1)Planning time: 0.080 msExecution time: 25144.538ms (7 rows) Time: 25145.185 ms test=# test=# test=# set enable_intmerge_sort to on; SET Time: 0.378 ms test=# explain analyze select * from (select * from stuff_text_asc order by randtxt offset 100000000000) foo; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------Limit (cost=247054.81..247054.81 rows=1 width=18) (actual time=1051.603..1051.603 rows=0 loops=1) -> Sort (cost=242054.81..247054.81 rows=2000001 width=18) (actual time=943.304..1006.988 rows=2000001 loops=1) Sort Key: stuff_text_asc.randtxt Sort Method: internal merge Memory: 221213kB Compares: 2000002 -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.009..98.474 rows=2000001 loops=1)Planning time: 0.072 msExecution time: 1063.434 ms (7 rows) Time: 1064.113 ms test=# test=# set enable_intmerge_sort to off; SET Time: 0.353 ms test=# test=# test=# test=# test=# test=# explain analyze select count(distinct randtxt) from stuff_text_asc; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=37739.01..37739.02 rows=1 width=18) (actual time=25196.814..25196.815 rows=1 loops=1) -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.010..114.995 rows=2000001 loops=1)Planning time: 0.053 msExecution time: 25196.857 ms (4 rows) Time: 25197.371 ms test=# test=# explain analyze select count(*) from (select distinct randtxt from stuff_text_asc) as foo; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=277054.83..277054.84 rows=1 width=0) (actual time=25521.258..25521.258 rows=1 loops=1) -> Unique (cost=242054.81..252054.81 rows=2000001 width=18) (actual time=25101.157..25438.622 rows=1999100 loops=1) -> Sort (cost=242054.81..247054.81 rows=2000001 width=18) (actual time=25101.156..25184.436 rows=2000001 loops=1) Sort Key: stuff_text_asc.randtxt Sort Method:quicksort Memory: 221213kB Compares: 95541376 -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.011..116.509 rows=2000001 loops=1)Planning time: 0.088 msExecution time: 25532.947ms (8 rows) Time: 25533.642 ms test=# test=# test=# set enable_intmerge_sort to on; SET Time: 0.401 ms test=# explain analyze select count(*) from (select distinct randtxt from stuff_text_asc) as foo; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=272054.82..272054.83 rows=1 width=0) (actual time=1184.289..1184.289 rows=1 loops=1) -> Sort (cost=242054.81..247054.81 rows=2000001 width=18) (actual time=1037.019..1100.720 rows=1999100 loops=1) Sort Key: stuff_text_asc.randtxt Sort Method: dedup internalmerge Memory: 221143kB Compares: 2000001 -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.010..106.729 rows=2000001 loops=1)Planning time: 0.086 msExecution time: 1195.891 ms (7 rows) Time: 1196.514 ms test=# -- Cheers, Jeremy
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: pg_dump gets attributes from tables in extensions