>-----Original Message----- >From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] >Sent: Monday, 25 February 2008 16:34 >To: Joris Dobbelsteen >Cc: Gregory Stark; Scott Marlowe; pgsql-general@xxxxxxxxxxxxxx >Subject: Re: Planner: rows=1 after "similar to" >where condition. > >"Joris Dobbelsteen" <Joris@xxxxxxxxxxxxxxxxxxxxx> writes: >> "Bitmap Heap Scan on log_syslog syslog (cost=11168.32..16988.84 >> rows=1 >> width=221) (actual time=11145.729..30067.606 rows=212 loops=1)" >> " Recheck Cond: (((program)::text = 'amavis'::text) AND >> ((facility)::text = 'mail'::text))" >> " Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~ >> '***:^(?:amavis\\[[0-9]+\\]: \\([0-9]+-[-0-9]+\\) Passed >[A-Za-z0-9]+, >> [][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>, >> (Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+, >> queued.as: [^ ,]+, [0-9]+ ms)$'::text))" > >It's not too surprising that you'd get a small selectivity >estimate for such a long regexp; the default estimate is just >based on the amount of fixed text in the pattern, and you've got a lot. > >If you increase the stats target for the column to 100 or more >then it will try actually applying the regexp to all the >histogram entries. >That might or might not give you a better estimate. I will try that, expect result back within a few days (have it collect some better sample set). Unfortunally the regex is not so much for narrowing down the selection, but rather guarenteeing the format of the messages. You seem to consider the common case differently, and I can agree for most part. Unfortunally my use-case is different from the expected. That said, might a less aggressive selectivity estimation for long strings work better in the common case? Might an alternative be to use a function and check for a positive result, i.e. something that the predictor cannot take into account? - Joris ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
