Re: Various performance questions
От | Dror Matalon |
---|---|
Тема | Re: Various performance questions |
Дата | |
Msg-id | 20031027071703.GF2979@rlx11.zapatec.com обсуждение исходный текст |
Ответ на | Re: Various performance questions (Christopher Browne <cbbrowne@acm.org>) |
Ответы |
Re: Various performance questions
(Shridhar Daithankar <shridhar_daithankar@persistent.co.in>)
|
Список | pgsql-performance |
On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote: > dror@zapatec.com (Dror Matalon) wrote: > > On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote: > >> Dror Matalon <dror@zapatec.com> writes: > >> > >> > explain analyze select count(*) from items where channel < 5000; > >> > QUERY PLAN > >> > -------------------------------------------------------------------------------------------------------------------------- > >> > Aggregate (cost=249141.54..249141.54 rows=1 width=0) (actual time=26224.603..26224.608 rows=1 loops=1) > >> > -> Seq Scan on items (cost=0.00..245377.52 rows=1505605 width=0) (actual time=7.599..17686.869 rows=1632057 loops=1) > >> > Filter: (channel < 5000) > >> > Total runtime: 26224.703 ms > >> > > >> > > >> > How can it do a sequential scan and apply a filter to it in less time > >> > than the full sequential scan? Is it actually using an index without > >> > really telling me? > >> > >> It's not using the index and not telling you. > >> > >> It's possible the count(*) operator itself is taking some time. Postgres > > > > I find it hard to believe that the actual counting would take a > > significant amount of time. > > Most of the time involves: > > a) Reading each page of the table, and > b) Figuring out which records on those pages are still "live." The table has been VACUUM ANALYZED so that there are no "dead" records. It's still not clear why select count() would be slower than select with a "where" clause. > > What work were you thinking was involved in doing the counting? I was answering an earlier response that suggested that maybe the actual counting took time so it would take quite a bit longer when there are more rows to count. > > >> doesn't have to call it on the rows that don't match the where clause. How > >> long does "explain analyze select 1 from items" with and without the where > >> clause take? > > > > Same as count(*). Around 55 secs with no where clause, around 25 secs > > with. > > Good; at least that's consistent... > -- > (format nil "~S@~S" "cbbrowne" "acm.org") > http://www3.sympatico.ca/cbbrowne/postgresql.html > Signs of a Klingon Programmer #2: "You question the worthiness of my > code? I should kill you where you stand!" > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com
В списке pgsql-performance по дате отправления: