Hi,
>You could always try
>
> CREATE INDEX test_index ON dbxref (accession varchar_pattern_ops);
WOW! we're now at runtime 0.367ms on Pg8
Next step is to initdb w/C Locale (tonight) (Thanks Tom et al.!).
Thanks again - will report back soon.
Susan
>From [EMAIL PROTECTED] Tue May 8 10:49:14 2007
X-Spam-Checker-Version: SpamAssassin 3.1.4 (2006-07-25) on borise.harvard.edu
X-Spam-Status: No, score=-1.7 required=3.0 tests=AWL,BAYES_00 autolearn=ham
version=3.1.4
X-Spam-Level:
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Date: Tue, 8 May 2007 16:48:34 +0200
From: "Steinar H. Gunderson" <[EMAIL PROTECTED]>
To: Susan Russo <[EMAIL PROTECTED]>
Cc: [email protected], [EMAIL PROTECTED]
Subject: Re: [PERFORM] specific query (not all) on Pg8 MUCH slower than Pg7
Mail-Followup-To: Susan Russo <[EMAIL PROTECTED]>,
[email protected], [EMAIL PROTECTED]
MIME-Version: 1.0
Content-Disposition: inline
X-Operating-System: Linux 2.6.20.4 on a x86_64
X-Message-Flag: Outlook? --> http://www.mozilla.org/products/thunderbird/
User-Agent: Mutt/1.5.13 (2006-08-11)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Mailing-List: pgsql-performance
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:[EMAIL PROTECTED]>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:[EMAIL PROTECTED]>
List-Post: <mailto:[email protected]>
List-Subscribe: <mailto:[EMAIL PROTECTED]>
List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
On Tue, May 08, 2007 at 10:18:34AM -0400, Susan Russo wrote:
> explain analyze output on Pg7.3.2:
>
> -> Index Scan using dbxref_idx2 on dbxref dx
> (cost=0.00..5.83 rows=1 width=21) (actual time=25.58..25.58 rows=0 loops=1)
> Index Cond: ((accession >= 'AY851043'::character
> varying) AND (accession < 'AY851044'::character varying))
> Filter: (accession ~~ 'AY851043%'::text)
>
> explain analyze output on Pg8.1.4:
>
> -> Seq Scan on dbxref dx (cost=0.00..47923.91 rows=1
> width=21) (actual time=2463.646..2463.646 rows=0 loops=1)
> Filter: ((accession)::text ~~ 'AY851043%'::text)
This is almost all of your cost. Did you perchance initdb the 8.1.4 cluster
in a non-C locale? You could always try
CREATE INDEX test_index ON dbxref (accession varchar_pattern_ops);
which would create an index that might be more useful for your LIKE query,
even in a non-C locale.
/* Steinar */
--
Homepage: http://www.sesse.net/
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
