From: David Johnston <polobo@yahoo.com>
To: 'Viktor Bojović' <viktor.bojovic@gmail.com>
To: pgsql-sql@postgresql.org
To: pgsql-admin@postgresql.org
Subject: Re: [SQL] function based index problem
Date: Wed, 31 Aug 2011 18:09:03 -0400
Message-ID: <02a401cc682a$99044210$cb0cc630$@yahoo.com> (raw)
In-Reply-To: <CAJu1cLZvOYadLj3g60Q6Tnqtjk1CZPjdr=T4E89MzUeWaR8TRg@mail.gmail.com>
References: <CAJu1cLZvOYadLj3g60Q6Tnqtjk1CZPjdr=T4E89MzUeWaR8TRg@mail.gmail.com>
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 ">=" 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=> explain select * from entry where length(sequence)=36805;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on entry (cost=1523.54..294886.26 rows=81226 width=1382)
Recheck Cond: (length((sequence)::text) = 36805)
-> Bitmap Index Scan on ndxlen (cost=0.00..1503.23 rows=81226 width=0)
Index Cond: (length((sequence)::text) = 36805)
(4 rows)
bioinf=> explain select * from entry where length(sequence)>=36805;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on entry (cost=0.00..5400995.21 rows=5415049 width=1382)
Filter: (length((sequence)::text) >= 36805)
(2 rows)
Thanx in advance
--
---------------------------------------
Viktor Bojović
---------------------------------------
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 “>=” query only returns 2 rows but the plan expects to return 5.4 MILLION – with that many results Sequential Scan is going to be faster than an Index
Either you have not run “ANALYZE” or you have more data than you think matching your criteria. Try “EXPLAIN ANALYZE” 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 “EXPLAIN ANALYZE” results so we can see exactly how many records each query returned.
David J.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, viktor.bojovic@gmail.com, pgsql-admin@postgresql.org
Subject: Re: [SQL] function based index problem
In-Reply-To: <02a401cc682a$99044210$cb0cc630$@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox