X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 61CA5D85EA for ; Mon, 10 Oct 2005 12:17:58 -0300 (ADT) 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 66226-06 for ; Mon, 10 Oct 2005 15:17:56 +0000 (GMT) Received: from liveye.net (host15-195-149-62.serverdedicati.aruba.it [62.149.195.15]) by svr1.postgresql.org (Postfix) with ESMTP id B1B26D79CF for ; Mon, 10 Oct 2005 12:17:55 -0300 (ADT) Received: from [192.168.1.33] by liveye.net (MDaemon.PRO.v7.2.3.R) with ESMTP id md50000031988.msg for ; Mon, 10 Oct 2005 17:17:55 +0200 Message-ID: <434A862C.9010701@liveye.net> Date: Mon, 10 Oct 2005 17:18:04 +0200 From: "Federico Simonetti (Liveye)" User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: it, it-it, en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Query performance on ILIKE with AND operator... References: <434A6C2D.9070207@liveye.net> <20804.1128956295@sss.pgh.pa.us> In-Reply-To: <20804.1128956295@sss.pgh.pa.us> X-Enigmail-Version: 0.93.0.0 Content-Type: multipart/alternative; boundary="------------070904090901040505090904" X-Authenticated-Sender: federico@liveye.net X-Spam-Processed: liveye.net, Mon, 10 Oct 2005 17:17:55 +0200 (not processed: spam filter disabled) X-MDRemoteIP: 213.140.22.64 X-Return-Path: federico@liveye.net X-MDaemon-Deliver-To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.303 required=5 tests=[AWL=-0.253, FORGED_RCVD_HELO=0.05, HTML_20_30=0.504, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200510/171 X-Sequence-Number: 14939 This is a multi-part message in MIME format. --------------070904090901040505090904 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 7bit Sorry but this does not seem to improve performance, it takes even more time, have a look at these data: explain analyze SELECT * FROM ViewHttp WHERE (vchost || txcontenttype) ilike '%www.%html%' ORDER BY iDStart DESC, iSensorID DESC, iForensicID DESC, iSubID DESC OFFSET 0 LIMIT 201 "Limit (cost=22740.77..22741.28 rows=201 width=1250) (actual time=14234.000..14234.000 rows=201 loops=1)" " -> Sort (cost=22740.77..22741.89 rows=447 width=1250) (actual time=14234.000..14234.000 rows=201 loops=1)" " Sort Key: detail0009.idstart, detail0009.isensorid, detail0009.iforensicid, detail0009.isubid" " -> Hash Join (cost=13.13..22721.10 rows=447 width=1250) (actual time=469.000..12140.000 rows=54035 loops=1)" " Hash Cond: ("outer".isensorid = "inner".isensorid)" " -> Nested Loop (cost=0.00..22701.27 rows=447 width=1165) (actual time=469.000..11428.000 rows=54035 loops=1)" " -> Seq Scan on detail0009 (cost=0.00..20763.77 rows=26 width=1005) (actual time=453.000..6345.000 rows=54064 loops=1)" " Filter: (((vchost)::text || txcontenttype) ~~* '%www.%html%'::text)" " -> Index Scan using connections_pkey on connections (cost=0.00..74.25 rows=18 width=168) (actual time=0.073..0.077 rows=1 loops=54064)" " Index Cond: (("outer".isensorid = connections.isensorid) AND ("outer".iforensicid = connections.iforensicid))" " -> Hash (cost=12.50..12.50 rows=250 width=101) (actual time=0.000..0.000 rows=0 loops=1)" " -> Seq Scan on sensors (cost=0.00..12.50 rows=250 width=101) (actual time=0.000..0.000 rows=1 loops=1)" "Total runtime: 14234.000 ms" Thanks for your help anyway... Federico Tom Lane ha scritto: >"Federico Simonetti (Liveye)" writes: > > >>I'm encountering a quite strange performance problem. >> >> > >The problem stems from the horrid misestimation of the number of rows >fetched from detail0009: > > > >>" -> Seq Scan on detail0009 (cost=0.00..20500.11 >>rows=26 width=1005) (actual time=453.000..5983.000 rows=53588 loops=1)" >>" Filter: ((txcontenttype ~~* '%html%'::text) >>AND ((vchost)::text ~~* '%www.%'::text))" >> >> > >When the planner is off by a factor of two thousand about the number of >rows involved, it's not very likely to produce a good plan :-( > >In the OR case the rowcount estimate is 6334, which is somewhat closer >to reality (only about a factor of 10 off, looks like), and that changes >the plan to something that works acceptably well. > >Assuming that this is web-log data, the prevalence of www and html >together is hardly surprising, but PG's statistical mechanisms will >never realize it. Not sure about a good workaround. Does it make >sense to combine the two conditions into one? > (vchost || txcontenttype) ilike '%www.%html%' > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 6: explain analyze is your friend > > > > --------------070904090901040505090904 Content-Type: text/html; charset=ISO-8859-15 Content-Transfer-Encoding: 8bit Sorry but this does not seem to improve performance, it takes even more time, have a look at these data:

explain analyze SELECT * FROM ViewHttp
WHERE (vchost || txcontenttype) ilike '%www.%html%'
ORDER BY iDStart DESC, iSensorID DESC, iForensicID DESC, iSubID DESC
OFFSET 0 LIMIT 201


"Limit  (cost=22740.77..22741.28 rows=201 width=1250) (actual time=14234.000..14234.000 rows=201 loops=1)"
"  ->  Sort  (cost=22740.77..22741.89 rows=447 width=1250) (actual time=14234.000..14234.000 rows=201 loops=1)"
"        Sort Key: detail0009.idstart, detail0009.isensorid, detail0009.iforensicid, detail0009.isubid"
"        ->  Hash Join  (cost=13.13..22721.10 rows=447 width=1250) (actual time=469.000..12140.000 rows=54035 loops=1)"
"              Hash Cond: ("outer".isensorid = "inner".isensorid)"
"              ->  Nested Loop  (cost=0.00..22701.27 rows=447 width=1165) (actual time=469.000..11428.000 rows=54035 loops=1)"
"                    ->  Seq Scan on detail0009  (cost=0.00..20763.77 rows=26 width=1005) (actual time=453.000..6345.000 rows=54064 loops=1)"
"                          Filter: (((vchost)::text || txcontenttype) ~~* '%www.%html%'::text)"
"                    ->  Index Scan using connections_pkey on connections  (cost=0.00..74.25 rows=18 width=168) (actual time=0.073..0.077 rows=1 loops=54064)"
"                          Index Cond: (("outer".isensorid = connections.isensorid) AND ("outer".iforensicid = connections.iforensicid))"
"              ->  Hash  (cost=12.50..12.50 rows=250 width=101) (actual time=0.000..0.000 rows=0 loops=1)"
"                    ->  Seq Scan on sensors  (cost=0.00..12.50 rows=250 width=101) (actual time=0.000..0.000 rows=1 loops=1)"
"Total runtime: 14234.000 ms"


Thanks for your help anyway...

Federico






Tom Lane ha scritto: 
"Federico Simonetti (Liveye)" <federico@liveye.net> writes:
  
I'm encountering a quite strange performance problem.
    

The problem stems from the horrid misestimation of the number of rows
fetched from detail0009:

  
"                    ->  Seq Scan on detail0009  (cost=0.00..20500.11
rows=26 width=1005) (actual time=453.000..5983.000 rows=53588 loops=1)"
"                          Filter: ((txcontenttype ~~* '%html%'::text)
AND ((vchost)::text ~~* '%www.%'::text))"
    

When the planner is off by a factor of two thousand about the number of
rows involved, it's not very likely to produce a good plan :-(

In the OR case the rowcount estimate is 6334, which is somewhat closer
to reality (only about a factor of 10 off, looks like), and that changes
the plan to something that works acceptably well.

Assuming that this is web-log data, the prevalence of www and html
together is hardly surprising, but PG's statistical mechanisms will
never realize it.  Not sure about a good workaround.  Does it make
sense to combine the two conditions into one?
	(vchost || txcontenttype) ilike '%www.%html%'

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--------------070904090901040505090904--