Re: [PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

2009-10-26 Thread Jesper Krogh
Craig Ringer wrote: > On 8.4 on a different system Pg uses the seq scan by preference, with a > runtime of 1148ms. It doesn't seem to want to do a bitmap heap scan when > searching for `commonterm' even when enable_seqscan is set to `off'. A > search for `commonterm80' also uses a seq scan (1067ms)

Re: [PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

2009-10-26 Thread Craig Ringer
On Tue, 2009-10-27 at 06:44 +0100, Jesper Krogh wrote: > Here you should search for "commonterm" not "commonterm80", commonterm > will go into a seq-scan. You're not testing the same thing as I did. Point taken. I ran the same commands as you, but as the planner picked different plans it wasn't m

Re: [PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

2009-10-26 Thread Jesper Krogh
Craig Ringer wrote: > On Tue, 2009-10-27 at 06:08 +0100, Jesper Krogh wrote: > >>> You should probably re-generate your random value for each call rather >>> than store it. Currently, every document with commonterm20 is guaranteed >>> to also have commonterm40, commonterm60, etc, which probably is

Re: [PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

2009-10-26 Thread Craig Ringer
On Tue, 2009-10-27 at 06:08 +0100, Jesper Krogh wrote: > > You should probably re-generate your random value for each call rather > > than store it. Currently, every document with commonterm20 is guaranteed > > to also have commonterm40, commonterm60, etc, which probably isn't very > > realistic,

Re: [PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

2009-10-26 Thread Jesper Krogh
Craig Ringer wrote: > On Mon, 2009-10-26 at 21:02 +0100, Jesper Krogh wrote: > >> Test system.. average desktop, 1 SATA drive and 1.5GB memory with pg 8.4.1. >> >> The dataset consists of words randomized, but .. all records contains >> "commonterm", around 80% contains commonterm80 and so on.. >>

Re: [PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

2009-10-26 Thread Craig Ringer
On Mon, 2009-10-26 at 21:02 +0100, Jesper Krogh wrote: > Test system.. average desktop, 1 SATA drive and 1.5GB memory with pg 8.4.1. > > The dataset consists of words randomized, but .. all records contains > "commonterm", around 80% contains commonterm80 and so on.. > > my $rand = rand();

[PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

2009-10-26 Thread Jesper Krogh
Hi. I'm currently trying to figure out why the tsearch performance seems to vary a lot between different queryplans. I have created a sample dataset that sort of resembles the data I have to work on. The script that builds the dataset is at: http://krogh.cc/~jesper/build-test.pl and http://krogh.

Re: [PERFORM] query planning different in plpgsql?

2009-10-26 Thread Waldomiro
Try to force a unique plan, like that: SELECT field, field2 ... FROM table1 WHERE field3 = 'xxx' AND field4 = 'yyy' AND field5 = 'zzz' so, in that example, I need the planner to use my field4 index, but the planner insists to use the field5, so I rewrite the query like this: SELECT field, fi

Re: [PERFORM] query planning different in plpgsql?

2009-10-26 Thread Tom Lane
"Michal J. Kubski" writes: > [ function that creates a bunch of temporary tables and immediately > joins them ] It'd probably be a good idea to insert an ANALYZE on the temp tables after you fill them. The way you've got this set up, there is no chance of auto-analyze correcting that oversight f

Re: [PERFORM] Full text search - query plan? PG 8.4.1

2009-10-26 Thread Tom Lane
Jesper Krogh writes: > According to the documentation the default cost is 1 for builin > functions and 100 for others, is this true for the ts-stuff also? Yeah. There was some recent discussion of pushing up the default cost for some of these obviously-not-so-cheap functions, but nothing's been

Re: [PERFORM] Full text search - query plan? PG 8.4.1

2009-10-26 Thread Jesper Krogh
Tom Lane wrote: > Jesper Krogh writes: >> Is is possible to manually set the cost for the @@ operator? > > You want to set the cost for the underlying function. alter function ts_match_vq(tsvector,tsquery) cost 500 seems to change my test-queries in a very positive way (e.g. resolve to bitmap i

Re: [PERFORM] query planning different in plpgsql?

2009-10-26 Thread Michal J . Kubski
On Mon, 26 Oct 2009 09:19:26 -0400, Merlin Moncure wrote: > On Mon, Oct 26, 2009 at 6:05 AM, Michal J. Kubski > wrote: >> On Fri, 23 Oct 2009 16:56:36 +0100, Grzegorz Jaśkiewicz >> wrote: >>> On Fri, Oct 23, 2009 at 4:49 PM, Scott Mead >>> wrote: >>> Do you not have an index o

Re: [PERFORM] query planning different in plpgsql?

2009-10-26 Thread Merlin Moncure
On Mon, Oct 26, 2009 at 6:05 AM, Michal J. Kubski wrote: > On Fri, 23 Oct 2009 16:56:36 +0100, Grzegorz Jaśkiewicz > wrote: >> On Fri, Oct 23, 2009 at 4:49 PM, Scott Mead >> wrote: >> >>> >>> >>>   Do you not have an index on last_snapshot.domain_id? >>> >> >> that, and also try rewriting a query

Re: [PERFORM] query planning different in plpgsql?

2009-10-26 Thread Michal J . Kubski
Hi, On Fri, 23 Oct 2009 16:56:36 +0100, Grzegorz Jaśkiewicz wrote: > On Fri, Oct 23, 2009 at 4:49 PM, Scott Mead > wrote: > >> >> >> Do you not have an index on last_snapshot.domain_id? >> > > that, and also try rewriting a query as JOIN. There might be difference in > performance/plan. >

Re: [PERFORM] optimizing query with multiple aggregates

2009-10-26 Thread Marc Mamin
Hello, I didn't try it, but following should be slightly faster: COUNT( CASE WHEN field >= x AND field < y THEN true END) intead of SUM( CASE WHEN field >= x AND field < y THEN 1 ELSE 0 END) HTH, Marc Mamin From: pgsql-performance-ow...@postgresql.org [m