X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 151809FB386 for ; Tue, 12 Sep 2006 15:46:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 60424-02-2 for ; Tue, 12 Sep 2006 18:43:53 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.mi8.com (d01gw04.mi8.com [63.240.6.44]) by postgresql.org (Postfix) with ESMTP id F167F9FB3C5 for ; Tue, 12 Sep 2006 15:37:15 -0300 (ADT) Received: from 172.16.1.148 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D4)); Tue, 12 Sep 2006 14:36:50 -0400 X-Server-Uuid: C8FB4D43-1108-484A-A898-3CBCC7906230 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.160]) by D01HOST02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 12 Sep 2006 14:36:12 -0400 Received: from 63.80.24.130 ([63.80.24.130]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.118]) with Microsoft Exchange Server HTTP-DAV ; Tue, 12 Sep 2006 18:36:11 +0000 User-Agent: Microsoft-Entourage/11.2.5.060620 Date: Tue, 12 Sep 2006 11:36:10 -0700 Subject: Re: tsearch2 question (was: Poor performance on seq From: "Luke Lonergan" To: "Laszlo Nagy" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] tsearch2 question (was: Poor performance on seq scan) Thread-Index: AcbWmlEQj43bAEKNEduoJAAWy4o9DA== In-Reply-To: <4506E7EC.8080605@designaproduct.biz> MIME-Version: 1.0 X-OriginalArrivalTime: 12 Sep 2006 18:36:12.0259 (UTC) FILETIME=[52697B30:01C6D69A] X-WSS-ID: 691821CA2US4118621-12-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.513 tagged_above=0 required=5 tests=BIZ_TLD, RCVD_NUMERIC_HELO X-Spam-Level: *** X-Archive-Number: 200609/97 X-Sequence-Number: 20783 Lazlo, On 9/12/06 10:01 AM, "Laszlo Nagy" wrote: > zeusd1=> explain analyze select id from product_search where name_desc > like '%Mug%'; > QUERY PLAN > ------------------------------------------------------------------------------ > ------------------------------------------ > Seq Scan on product_search (cost=0.00..54693.34 rows=36487 width=8) > (actual time=20.036..2541.971 rows=91399 loops=1) > Filter: (name_desc ~~ '%Mug%'::text) > Total runtime: 2581.272 ms > (3 rows) > > The total runtime remains below 3 sec in all cases. By creating a table with only the name field you are searching, you have just reduced the size of rows so that they fit in memory. That is why your query runs faster. If your searched data doesn't grow, this is fine. If it does, you will need to fix your disk drive OS problem. - Luke