pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction based index problem
5+ messages / 3 participants
[nested] [flat]
* function based index problem
@ 2011-08-31 21:27 Viktor Bojović <viktor.bojovic@gmail.com>
2011-08-31 21:53 ` Re: function based index problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-08-31 22:09 ` Re: [SQL] function based index problem David Johnston <polobo@yahoo.com>
0 siblings, 2 replies; 5+ messages in thread
From: Viktor Bojović @ 2011-08-31 21:27 UTC (permalink / raw)
To: pgsql-sql; pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function based index problem
2011-08-31 21:27 function based index problem Viktor Bojović <viktor.bojovic@gmail.com>
@ 2011-08-31 21:53 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-08-31 22:21 ` Re: function based index problem Viktor Bojović <viktor.bojovic@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2011-08-31 21:53 UTC (permalink / raw)
To: Viktor Bojovi* <viktor.bojovic@gmail.com>; pgsql-admin@postgresql.org; pgsql-sql
Viktor Bojovi*<viktor.bojovic@gmail.com> wrote:
> i don't know why it doesn't use index scan.
Because it thinks your query will return 81226 rows. We need more
information to make many suggestions beyond "make sure the table has
been analyzed".
http://wiki.postgresql.org/wiki/SlowQueryQuestions
Also, it's not considered good form to post an issue to more than
one PostgreSQL list. This is probably most appropriate on the
pgsql-performance list, so if you want to follow up with more detail
to get more detailed suggestions, it would be best to abandon this
thread and start a new one on that list.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function based index problem
2011-08-31 21:27 function based index problem Viktor Bojović <viktor.bojovic@gmail.com>
2011-08-31 21:53 ` Re: function based index problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-08-31 22:21 ` Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Viktor Bojović @ 2011-08-31 22:21 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin@postgresql.org
On Wed, Aug 31, 2011 at 11:53 PM, Kevin Grittner <
Kevin.Grittner@wicourts.gov> wrote:
> Viktor Bojovi*<viktor.bojovic@gmail.com> wrote:
>
> > i don't know why it doesn't use index scan.
>
> Because it thinks your query will return 81226 rows. We need more
> information to make many suggestions beyond "make sure the table has
> been analyzed".
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions
>
> Also, it's not considered good form to post an issue to more than
> one PostgreSQL list. This is probably most appropriate on the
> pgsql-performance list, so if you want to follow up with more detail
> to get more detailed suggestions, it would be best to abandon this
> thread and start a new one on that list.
>
> -Kevin
>
"analyze entry" has solved the problem. Thanx a lot.
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] function based index problem
2011-08-31 21:27 function based index problem Viktor Bojović <viktor.bojovic@gmail.com>
@ 2011-08-31 22:09 ` David Johnston <polobo@yahoo.com>
2011-08-31 22:17 ` Re: function based index problem Viktor Bojović <viktor.bojovic@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: David Johnston @ 2011-08-31 22:09 UTC (permalink / raw)
To: 'Viktor Bojović' <viktor.bojovic@gmail.com>; pgsql-sql; pgsql-admin@postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function based index problem
2011-08-31 21:27 function based index problem Viktor Bojović <viktor.bojovic@gmail.com>
2011-08-31 22:09 ` Re: [SQL] function based index problem David Johnston <polobo@yahoo.com>
@ 2011-08-31 22:17 ` Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Viktor Bojović @ 2011-08-31 22:17 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-08-31 22:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-31 21:27 function based index problem Viktor Bojović <viktor.bojovic@gmail.com>
2011-08-31 21:53 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-08-31 22:21 ` Viktor Bojović <viktor.bojovic@gmail.com>
2011-08-31 22:09 ` David Johnston <polobo@yahoo.com>
2011-08-31 22:17 ` Viktor Bojović <viktor.bojovic@gmail.com>
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