pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
slow count() was: tsearch2 poor performance
3+ messages / 2 participants
[nested] [flat]

* slow count() was: tsearch2 poor performance
@ 2004-10-03 20:30 Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 0 replies; 3+ messages in thread

From: Oleg Bartunov @ 2004-10-03 20:30 UTC (permalink / raw)
  To: Kris Kiger <kris@musicrebellion.com>; +Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>; pgsql-admin

On Fri, 1 Oct 2004, Kris Kiger 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 :(




	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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [HACKERS] slow count() was: tsearch2 poor performance
@ 2004-10-03 20:43 Magnus Hagander <mha@sollentuna.net>
  2004-10-03 20:51 ` Re: [HACKERS] slow count() was: tsearch2 poor performance Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 1 reply; 3+ messages in thread

From: Magnus Hagander @ 2004-10-03 20:43 UTC (permalink / raw)
  To: Oleg Bartunov <oleg@sai.msu.su>; Kris Kiger <kris@musicrebellion.com>; +Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>; pgsql-admin

>> 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.

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)


At least that's how I think LIMIT works. That would certainly explain
the major time difference.

//Magnus



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [HACKERS] slow count() was: tsearch2 poor performance
  2004-10-03 20:43 Re: [HACKERS] slow count() was: tsearch2 poor performance Magnus Hagander <mha@sollentuna.net>
@ 2004-10-03 20:51 ` Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 0 replies; 3+ messages in thread

From: Oleg Bartunov @ 2004-10-03 20:51 UTC (permalink / raw)
  To: Magnus Hagander <mha@sollentuna.net>; +Cc: Kris Kiger <kris@musicrebellion.com>; Pgsql Hackers <pgsql-hackers@postgresql.org>; pgsql-admin


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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-10-03 20:51 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-03 20:30 slow count() was: tsearch2 poor performance Oleg Bartunov <oleg@sai.msu.su>
2004-10-03 20:43 Re: [HACKERS] slow count() was: tsearch2 poor performance Magnus Hagander <mha@sollentuna.net>
2004-10-03 20:51 ` Oleg Bartunov <oleg@sai.msu.su>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox