[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] function based index problem
From: Viktor_Bojović <viktor.bojovic () gmail ! com>
Date: 2011-08-31 22:17:57
Message-ID: CAJu1cLb90W_54yQ7NBEjhTT4w=1jX=xYDMpnwKVVfRnq9kiF5A () mail ! gmail ! com
[Download RAW message or body]
On Thu, Sep 1, 2011 at 12:09 AM, David Johnston <polobo@yahoo.com> wrote:
> ** **
>
> *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.****
>
> ** **
>
It works now after "analyze entry" was executed. thanx a lot.
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
[Attachment #3 (text/html)]
<br><br><div class="gmail_quote">On Thu, Sep 1, 2011 at 12:09 AM, David Johnston \
<span dir="ltr"><<a href="mailto:polobo@yahoo.com">polobo@yahoo.com</a>></span> \
wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, \
204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> <div link="blue" vlink="purple" \
lang="EN-US"><div><p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, \
73, 125);"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left: \
0.5in;"><b><span style="font-size: 10pt;">From:</span></b><span style="font-size: \
10pt;"> <a href="mailto:pgsql-sql-owner@postgresql.org" \
target="_blank">pgsql-sql-owner@postgresql.org</a> [mailto:<a \
href="mailto:pgsql-sql-owner@postgresql.org" \
target="_blank">pgsql-sql-owner@postgresql.org</a>] <b>On Behalf Of </b>Viktor \
Bojovic<br> <b>Sent:</b> Wednesday, August 31, 2011 5:27 PM<br><b>To:</b> <a \
href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>; \
<a href="mailto:pgsql-admin@postgresql.org" \
target="_blank">pgsql-admin@postgresql.org</a><br> <b>Subject:</b> [SQL] function \
based index problem<u></u><u></u></span></p><div><div></div><div class="h5"><p \
class="MsoNormal" style="margin-left: 0.5in;"><u></u> <u></u></p><p \
class="MsoNormal" style="margin-left: 0.5in;"> Hi,<br>on table entry (17M records) \
there is one index:<br><br>CREATE INDEX ndxlen<br> ON uniprot_frekvencije.entry<br> \
USING btree<br> (length(sequence::text));<br clear="all"><br>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.<br> \
<br>bioinf=> explain select * from entry where length(sequence)=36805;<br> \
QUERY PLAN \
<br>----------------------------------------------------------------------------<br> \
Bitmap Heap Scan on entry (cost=1523.54..294886.26 rows=81226 width=1382)<br> \
Recheck Cond: (length((sequence)::text) = 36805)<br> -> Bitmap Index Scan on \
ndxlen (cost=0.00..1503.23 rows=81226 width=0)<br> Index Cond: \
(length((sequence)::text) = 36805)<br> (4 rows)<br><br>bioinf=> explain select * \
from entry where length(sequence)>=36805;<br> \
QUERY PLAN \
<br>--------------------------------------------------------------------<br> Seq \
Scan on entry (cost=0.00..5400995.21 rows=5415049 width=1382)<br> Filter: \
(length((sequence)::text) >= 36805)<br>(2 rows)<br><br>Thanx in advance<br>-- \
<br>---------------------------------------<br>Viktor Bojović<br>
---------------------------------------<br>Wherever I go, Murphy goes with \
me<u></u><u></u></p><div style="border-style: none none solid; border-color: \
-moz-use-text-color -moz-use-text-color windowtext; border-width: medium medium 1pt; \
padding: 0in 0in 1pt;"> <p class="MsoNormal" style="border: medium none ; padding: \
0in;"><span style="font-size: 11pt; color: rgb(31, 73, 125);"><u></u> \
<u></u></span></p></div></div></div><p class="MsoNormal"><span style="font-size: \
11pt; color: rgb(31, 73, 125);">Some \
observations/suggestions:<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size: 11pt; color: rgb(31, 73, 125);"><u></u> <u></u></span></p><p \
class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">Please do \
not Cross-Post<u></u><u></u></span></p> <p class="MsoNormal"><span style="font-size: \
11pt; color: rgb(31, 73, 125);">You have not provided your PostgreSQL \
version<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size: 11pt; \
color: rgb(31, 73, 125);"><u></u> <u></u></span></p> <p class="MsoNormal"><span \
style="font-size: 11pt; color: rgb(31, 73, 125);">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<u></u><u></u></span></p> <p \
class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">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.<u></u><u></u></span></p> <p class="MsoNormal"><span style="font-size: 11pt; \
color: rgb(31, 73, 125);"><u></u> <u></u></span></p><p class="MsoNormal"><span \
style="font-size: 11pt; color: rgb(31, 73, 125);">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.<u></u><u></u></span></p> <p class="MsoNormal"><span style="font-size: 11pt; \
color: rgb(31, 73, 125);"><u></u> <u></u></span></p><p class="MsoNormal"><span \
style="font-size: 11pt; color: rgb(31, 73, 125);">David J.<u></u><u></u></span></p><p \
class="MsoNormal"> <span style="font-size: 11pt; color: rgb(31, 73, 125);"><u></u> \
<u></u></span></p></div></div></blockquote></div><br>It works now after "analyze \
entry" was executed. thanx a lot.<br><br clear="all"><br>-- \
<br>---------------------------------------<br> Viktor \
Bojović<br>---------------------------------------<br>Wherever I go, Murphy goes \
with me<br>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic