Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 9BD08B5DC5F for ; Wed, 31 Aug 2011 19:09:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 64991-10 for ; Wed, 31 Aug 2011 22:09:29 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp103.prem.mail.sp1.yahoo.com (smtp103.prem.mail.sp1.yahoo.com [98.136.44.58]) by mail.postgresql.org (Postfix) with SMTP id 48634B5DC6A for ; Wed, 31 Aug 2011 19:09:28 -0300 (ADT) Received: (qmail 99820 invoked from network); 31 Aug 2011 22:09:27 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language; b=omQlsS1GIF/KKvssja393sMlmIy/S5m8DTFGHQg5jgb+4PX2ALm97eH2jHDNbxJzHuRWUy4lGT2HsVpChLZgORTVdAPKPk0+xxO2iknKlktWGjoHMEfY5g5+e8fMmBiAmpFYeUoTgLnQI2ntSPDNN2Mypy0aDtRbjzpeHHL8kio= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1314828567; bh=eAIf0Wck+m1pDV4rHTMcHQNCH/g1XEwXhVDzXbR41Yc=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language; b=ovxE2xUHrjRqooeUNRZePNCLayJmOWlQvar2FN0IBDf45Xz0Ow3m30ZN//sYdRIacLVUakC0nN19aIQonrLsqr33dxVQcMbd6Pff55AJc6e0W2vJ88CdOrHLuOsChc2QkwHHlX+M4IZujw5cUOfpbamyUXvGeKA6Er1VXKGkLOk= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: zD_gnPkVM1k.9DikbtKJ0dExp.jMcyEoakerT2X1QnSvTag aCW_YE7nv7SwbPH6B42rcoUedI8h_CevDzF6DWRP5iRPot2R.nvA9nvmBaL4 LRY3ojFsP1SHhSL_KcBi_o5iBr6Dqm1KAgqS5RoLPUgb2YJnjadlKyBbaUOs dAO1Rtlf9ASR3cbqe6PhWzrRLLPAEpWRlE8Sb6L5Gj04Ny6iYcnl8XNW3C2s 3laLeFEKvmZSqBdpGcXTSca7tv90VGQ4ewbYwCbb8k2rM2t5cDxoMYgQ1PWh .B7Nw1x5I37sCbZbALd7ZV4aiJs8OeyMxMho0vhd.0WCLiPxh7qsR28IcNk4 DIZqsChlrYkP91wgKsVq7YlzFdQl6.m.3CYD9VaWclQC__IEeYoQoQhOjDHa GPL0A1Q-- X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from WolfDog (polobo@66.67.24.143 with login) by smtp103.prem.mail.sp1.yahoo.com with SMTP; 31 Aug 2011 15:09:26 -0700 PDT From: "David Johnston" To: =?UTF-8?Q?'Viktor_Bojovi=C4=87'?= , , References: In-Reply-To: Subject: Re: [SQL] function based index problem Date: Wed, 31 Aug 2011 18:09:03 -0400 Message-ID: <02a401cc682a$99044210$cb0cc630$@yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_02A5_01CC6809.11F428B0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQJTRCZUqWTf5JIpFPD/tS7mSP6/rpQpySFQ Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.401 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-0.504, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 201108/249 X-Sequence-Number: 38236 This is a multipart message in MIME format. ------=_NextPart_000_02A5_01CC6809.11F428B0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =20 From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Viktor Bojovic Sent: Wednesday, August 31, 2011 5:27 PM To: pgsql-sql@postgresql.org; pgsql-admin@postgresql.org Subject: [SQL] function based index problem =20 Hi, on table entry (17M records) there is one index: CREATE INDEX ndxlen ON uniprot_frekvencije.entry USING btree (length(sequence::text)); When using ">=3D" in search which returns only two records, query runs = much (hundred times) slower. i don't know why it doesn't use index scan. = I just wanted to ask how can i modify the query to use that index? = Explain plans are pasted below. bioinf=3D> explain select * from entry where length(sequence)=3D36805; QUERY PLAN = =20 -------------------------------------------------------------------------= --- Bitmap Heap Scan on entry (cost=3D1523.54..294886.26 rows=3D81226 = width=3D1382) Recheck Cond: (length((sequence)::text) =3D 36805) -> Bitmap Index Scan on ndxlen (cost=3D0.00..1503.23 rows=3D81226 = width=3D0) Index Cond: (length((sequence)::text) =3D 36805) (4 rows) bioinf=3D> explain select * from entry where length(sequence)>=3D36805; QUERY PLAN =20 -------------------------------------------------------------------- Seq Scan on entry (cost=3D0.00..5400995.21 rows=3D5415049 = width=3D1382) Filter: (length((sequence)::text) >=3D 36805) (2 rows) Thanx in advance --=20 --------------------------------------- Viktor Bojovi=C4=87 --------------------------------------- Wherever I go, Murphy goes with me =20 Some observations/suggestions: =20 Please do not Cross-Post You have not provided your PostgreSQL version =20 You state the =E2=80=9C>=3D=E2=80=9D query only returns 2 rows but the = plan expects to return 5.4 MILLION =E2=80=93 with that many results = Sequential Scan is going to be faster than an Index Either you have not run =E2=80=9CANALYZE=E2=80=9D or you have more data = than you think matching your criteria. Try =E2=80=9CEXPLAIN = ANALYZE=E2=80=9D to actually run the query and see what you get. =20 It is likely that a simple ANALYZE on the table will solve your problem = (ALWAYS RUN ANALYZE BEFORE POSTING QUESTIONS LIKE THIS); in the unlikely = event it does not please post the =E2=80=9CEXPLAIN ANALYZE=E2=80=9D = results so we can see exactly how many records each query returned. =20 David J. =20 ------=_NextPart_000_02A5_01CC6809.11F428B0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

 

From:= = pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of Viktor Bojovic
Sent: Wednesday, August 31, = 2011 5:27 PM
To: pgsql-sql@postgresql.org; = pgsql-admin@postgresql.org
Subject: [SQL] function based index = problem

 

Hi,
on table entry (17M records) there is = one index:

CREATE INDEX ndxlen
  ON = uniprot_frekvencije.entry
  USING btree
  = (length(sequence::text));

When using = ">=3D" in search which returns only two records, query runs = much (hundred times) slower. i don't know why it doesn't use index scan. = I just wanted to ask how can i modify the query to use that index? = Explain plans are pasted below.

bioinf=3D> explain select * = from entry where = length(sequence)=3D36805;
       &n= bsp;           &nb= sp;           &nbs= p; QUERY = PLAN           &nb= sp;           &nbs= p;         =
---------------------------------------------------------------------= -------
 Bitmap Heap Scan on entry  = (cost=3D1523.54..294886.26 rows=3D81226 width=3D1382)
   = Recheck Cond: (length((sequence)::text) =3D 36805)
   = ->  Bitmap Index Scan on ndxlen  (cost=3D0.00..1503.23 = rows=3D81226 = width=3D0)
         Index = Cond: (length((sequence)::text) =3D 36805)
(4 = rows)

bioinf=3D> explain select * from entry where = length(sequence)>=3D36805;
      &nbs= p;            = ;          QUERY = PLAN           &nb= sp;           &nbs= p;     =
--------------------------------------------------------------------<= br> Seq Scan on entry  (cost=3D0.00..5400995.21 rows=3D5415049 = width=3D1382)
   Filter: (length((sequence)::text) >=3D = 36805)
(2 rows)

Thanx in advance
-- =
---------------------------------------
Viktor = Bojovi=C4=87
---------------------------------------
Wherever I = go, Murphy goes with me

 

Some observations/suggestions:

 

Please do not Cross-Post

You have not provided your PostgreSQL version

 

You state the =E2=80=9C>=3D=E2=80=9D query only returns 2 rows but = the plan expects to return 5.4 MILLION =E2=80=93 with that many results = Sequential Scan is going to be faster than an = Index

Either you have not run =E2=80=9CANALYZE=E2=80=9D or you have more = data than you think matching your criteria.=C2=A0 Try =E2=80=9CEXPLAIN = ANALYZE=E2=80=9D to actually run the query and see what you = get.

 

It is likely that a simple ANALYZE on the table will solve your = problem (ALWAYS RUN ANALYZE BEFORE POSTING QUESTIONS LIKE THIS); in the = unlikely event it does not please post the =E2=80=9CEXPLAIN = ANALYZE=E2=80=9D results so we can see exactly how many records each = query returned.

 

David J.

 

------=_NextPart_000_02A5_01CC6809.11F428B0--