X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8837FD1BABD for ; Wed, 21 Apr 2004 08:50:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 22221-07 for ; Wed, 21 Apr 2004 08:50:16 -0300 (ADT) Received: from FE-mail04.sfg.albacom.net (FE-mail04.albacom.net [213.217.149.84]) by svr1.postgresql.org (Postfix) with ESMTP id AB941D1B482 for ; Wed, 21 Apr 2004 08:50:13 -0300 (ADT) Received: from axa.it (217.221.80.72) by FE-mail04.sfg.albacom.net (7.0.009) id 40757DA1000F5C81; Wed, 21 Apr 2004 13:50:05 +0200 Message-ID: <40865FEC.9030500@axa.it> Date: Wed, 21 Apr 2004 13:50:04 +0200 From: Edoardo Ceccarelli User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: it, en, en-us MIME-Version: 1.0 To: Christopher Kings-Lynne Cc: Nick Barr , pgsql-performance@postgresql.org Subject: Re: slow seqscan References: <40862003.2090201@axa.it> <4086272E.2080102@chuckie.co.uk> <40863228.907@axa.it> <408636A5.2050305@familyhealth.com.au> <408641B7.5090808@axa.it> <4086487A.4090703@axa.it> <408649C1.3000904@familyhealth.com.au> In-Reply-To: <408649C1.3000904@familyhealth.com.au> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/342 X-Sequence-Number: 6642 just created a copy of the same database and it shows that is the analyze that's messing things: Slow seqscan query executed on dba400 dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric = 'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..3116.00 rows=11 width=546) (actual time=46.66..51.40 rows=11 loops=1) -> Seq Scan on annuncio400 (cost=0.00..35490.60 rows=125 width=546) (actual time=46.66..51.38 rows=12 loops=1) Filter: ((rubric = 'DD'::bpchar) AND (lower((testo)::text) ~~ 'cbr%'::text)) Total runtime: 51.46 msec (4 rows) fastest index scan query on dba400b (exact copy of dba400) dba400b=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric = 'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..7058.40 rows=9 width=546) (actual time=1.36..8.18 rows=11 loops=1) -> Index Scan using rubric on annuncio400 (cost=0.00..7369.42 rows=9 width=546) (actual time=1.35..8.15 rows=12 loops=1) Index Cond: (rubric = 'DD'::bpchar) Filter: (lower((testo)::text) ~~ 'cbr%'::text) Total runtime: 8.28 msec (5 rows) what about this index you suggested? it gives me sintax error while trying to create it: CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric, LOWER(testo)); Thanks Edoardo Christopher Kings-Lynne ha scritto: > >> enable_seqscan = false >> >> and I'm having all index scans, timing has improved from 600ms to 18ms >> >> wondering what other implications I might expect. > > > Lots of really bad implications...it's really not a good idea. > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match > > >