X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6D4FE329F36; Sun, 3 Oct 2004 21:52:11 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58256-01; Sun, 3 Oct 2004 20:52:02 +0000 (GMT) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by svr1.postgresql.org (Postfix) with ESMTP id 66254329F3B; Sun, 3 Oct 2004 21:51:56 +0100 (BST) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.12.10/8.12.10) with ESMTP id i93KplQT018888; Mon, 4 Oct 2004 00:51:47 +0400 (MSD) Date: Mon, 4 Oct 2004 00:51:47 +0400 (MSD) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Magnus Hagander Cc: Kris Kiger , Pgsql Hackers , pgsql-admin@postgresql.org Subject: Re: [HACKERS] slow count() was: tsearch2 poor performance In-Reply-To: <6BCB9D8A16AC4241919521715F4D8BCE475DE4@algol.sollentuna.se> Message-ID: References: <6BCB9D8A16AC4241919521715F4D8BCE475DE4@algol.sollentuna.se> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/75 X-Sequence-Number: 15068 Magnus On Sun, 3 Oct 2004, Magnus Hagander wrote: > >> Hey all, its me again. If I do not do a count(product_id) on my > >> tsearch2 queries, its actually really fast, for example; > >> > > > >Hmm, I also really want to know what's the difference ? > >Postgresql 8.0beta3 on Linux 2.4.25 > > > >tsearchd=# explain analyze select body from txt where > >fts_index @@ to_tsquery('oil') limit 1000; > > QUERY PLAN > >--------------------------------------------------------------- > >----------------------------------------------------------------- > > Limit (cost=0.00..4027.67 rows=1000 width=315) (actual > >time=0.053..14.662 rows=1000 loops=1) > > -> Index Scan using fts_idx on txt (cost=0.00..12083.02 > >rows=3000 width=315) (actual time=0.049..12.552 rows=1000 loops=1) > > Index Cond: (fts_index @@ '\'oil\''::tsquery) > > Total runtime: 15.848 ms > >(4 rows) > > > >tsearchd=# explain analyze select count(body) from txt where > >fts_index @@ to_tsquery('oil') limit 1000; > > > >Didn't get result after 10 minutes :( > > > > I think you're missing what LIMIT does. > > In the first query, it LIMITs the return from the index scan to 1000 > entries. > In the second query, it LIMITs the return from the aggregate to 1000 > entries. The indexscan will include all matches, send then to count(), > which returns 1 row only, which LIMIT is then applied to. > Thanks for explanation. I suspect this, so LIMIT is still hack > You could probably reach the same result with a subselect: > select count(*) FROM (select body from txt where fts_index @@ > to_tsquery('oil') limit 1000) > yes, timings now are equal ! > > At least that's how I think LIMIT works. That would certainly explain > the major time difference. > > //Magnus > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83