pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex doesn't appear to be working.
7+ messages / 5 participants
[nested] [flat]
* Index doesn't appear to be working.
@ 2002-03-03 00:28 John Oakes <john@networkproductions.net>
0 siblings, 1 reply; 7+ messages in thread
From: John Oakes @ 2002-03-03 00:28 UTC (permalink / raw)
To: pgsql-sql
I have a table with a column titled 'passfail' that only contains either a P
for pass or an F for fail. The table name is 'one'. I created the index on
the table with:
CREATE INDEX one_passfail_idx ON one USING btree (passfail);
I then do:
VACUUM ANALYZE one;
Then I do an explain on this query:
SELECT * FROM one where passfail = 'P';
and it tells me:
Seq Scan on one (cost=0.00..263.02 rows=5613 width=56)
Shouldn't it tell me
Index Scan using one_passfail_idx on one?
Why isn't it using the index? This query returns about 5,600 of 10,000
records. It is faster to just do a seq scan because it returns such a high
percentage or records? Thanks, I appreciate the help!
John Oakes
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index doesn't appear to be working.
@ 2002-03-03 07:50 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: John Oakes <john@networkproductions.net>
0 siblings, 2 replies; 7+ messages in thread
From: Stephan Szabo @ 2002-03-03 07:50 UTC (permalink / raw)
To: John Oakes <john@networkproductions.net>; +Cc: pgsql-sql
On Sat, 2 Mar 2002, John Oakes wrote:
> I have a table with a column titled 'passfail' that only contains either a P
> for pass or an F for fail. The table name is 'one'. I created the index on
> the table with:
>
> CREATE INDEX one_passfail_idx ON one USING btree (passfail);
>
> I then do:
>
> VACUUM ANALYZE one;
>
> Then I do an explain on this query:
>
> SELECT * FROM one where passfail = 'P';
>
> and it tells me:
>
> Seq Scan on one (cost=0.00..263.02 rows=5613 width=56)
>
> Shouldn't it tell me
>
> Index Scan using one_passfail_idx on one?
>
> Why isn't it using the index? This query returns about 5,600 of 10,000
> records. It is faster to just do a seq scan because it returns such a high
> percentage or records? Thanks, I appreciate the help!
Usually the seq scan will be faster in this kind of situation. Because
the system needs to load the matching rows anyway, it's likely to result
in reading all the blocks of the table and paying a penalty for seeking
around the file.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index doesn't appear to be working.
@ 2002-03-03 16:02 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2002-03-03 16:02 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: John Oakes <john@networkproductions.net>; pgsql-sql
> > Why isn't it using the index? This query returns about 5,600 of 10,000
> > records. It is faster to just do a seq scan because it returns such a high
> > percentage or records? Thanks, I appreciate the help!
>
> Usually the seq scan will be faster in this kind of situation. Because
> the system needs to load the matching rows anyway, it's likely to result
> in reading all the blocks of the table and paying a penalty for seeking
> around the file.
We are getting this "no index use" question over and over again, and I
was wondering why the FAQ item was not answering this question for
people. It turns out the wording was not very clear and I have updated
this FAQ item to more clearly answer the question. Seems I kept adding
to that item without restructuring the information:
<H4><A name="4.8">4.8</A>) My queries are slow or don't make use of
the indexes. Why?</H4>
Indexes are not automatically used by every query. Indexes are only
used if the table is larger than a minimum size, and the index
selects only a small percentage of the rows in the table. This is
because the random disk access caused by an index scan is sometimes
slower than a straight read through the table, or sequential scan.
<P>To determine if an index should be used, PostgreSQL must have
statistics about the table. These statistics are collected using
<SMALL>VACUUM ANALYZE</SMALL>, or simply <SMALL>ANALYZE</SMALL>.
Using statistics, the optimizer knows how many rows are in the
table, and can better determine if indexes should be used.
Statistics are also valuable in determining optimal join order and
join methods. Statistics collection should be performed periodically
as the contents of the table change.
<P>Indexes are normally not used for <SMALL>ORDER BY</SMALL> or to
perform joins. A sequential scan followed by an explicit sort is
usually faster than an index scan of a large table.</P> However,
<SMALL>LIMIT</SMALL> combined with <SMALL>ORDER BY</SMALL> often
will use an index because only a small portion of the table is
returned.
<P>When using wild-card operators such as <SMALL>LIKE</SMALL> or
<I>~</I>, indexes can only be used if the beginning of the search is
anchored to the start of the string. Therefore, to use indexes,
<SMALL>LIKE</SMALL> patterns must not start with <I>%</I>, and
<I>~</I>(regular expression) patterns must start with <I>^</I>.</P>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index doesn't appear to be working.
@ 2002-03-03 16:58 Wei Weng <wweng@kencast.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Wei Weng @ 2002-03-03 16:58 UTC (permalink / raw)
To: pgsql-sql
Is there any plan of making Postgresql collecting statistics a little more
automatically?
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Sunday, March 03, 2002 11:03 AM
To: Stephan Szabo
Cc: John Oakes; pgsql-sql@postgresql.org
Subject: Re: [SQL] Index doesn't appear to be working.
> > Why isn't it using the index? This query returns about 5,600 of 10,000
> > records. It is faster to just do a seq scan because it returns such a
high
> > percentage or records? Thanks, I appreciate the help!
>
> Usually the seq scan will be faster in this kind of situation. Because
> the system needs to load the matching rows anyway, it's likely to result
> in reading all the blocks of the table and paying a penalty for seeking
> around the file.
We are getting this "no index use" question over and over again, and I
was wondering why the FAQ item was not answering this question for
people. It turns out the wording was not very clear and I have updated
this FAQ item to more clearly answer the question. Seems I kept adding
to that item without restructuring the information:
<H4><A name="4.8">4.8</A>) My queries are slow or don't make use of
the indexes. Why?</H4>
Indexes are not automatically used by every query. Indexes are only
used if the table is larger than a minimum size, and the index
selects only a small percentage of the rows in the table. This is
because the random disk access caused by an index scan is sometimes
slower than a straight read through the table, or sequential scan.
<P>To determine if an index should be used, PostgreSQL must have
statistics about the table. These statistics are collected using
<SMALL>VACUUM ANALYZE</SMALL>, or simply <SMALL>ANALYZE</SMALL>.
Using statistics, the optimizer knows how many rows are in the
table, and can better determine if indexes should be used.
Statistics are also valuable in determining optimal join order and
join methods. Statistics collection should be performed periodically
as the contents of the table change.
<P>Indexes are normally not used for <SMALL>ORDER BY</SMALL> or to
perform joins. A sequential scan followed by an explicit sort is
usually faster than an index scan of a large table.</P> However,
<SMALL>LIMIT</SMALL> combined with <SMALL>ORDER BY</SMALL> often
will use an index because only a small portion of the table is
returned.
<P>When using wild-card operators such as <SMALL>LIKE</SMALL> or
<I>~</I>, indexes can only be used if the beginning of the search is
anchored to the start of the string. Therefore, to use indexes,
<SMALL>LIKE</SMALL> patterns must not start with <I>%</I>, and
<I>~</I>(regular expression) patterns must start with <I>^</I>.</P>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index doesn't appear to be working.
@ 2002-03-03 18:26 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Wei Weng <wweng@kencast.com>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2002-03-03 18:26 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
> Is there any plan of making Postgresql collecting statistics a little
> more automatically?
Well, most of the commerical OS's require some manual intervention. I
don't know how we could do any better than we are doing now, but we are
open to suggestions.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index doesn't appear to be working.
@ 2002-03-03 18:36 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2002-03-03 18:36 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
Frank Bax wrote:
> At 11:02 AM 03/03/02 -0500, Bruce Momjian wrote:
> > Indexes are not automatically used by every query. Indexes are only
> > used if the table is larger than a minimum size, and the index
> > selects only a small percentage of the rows in the table.
>
>
> Shouldn't it say:
> "and the *query* selects only a small percentage..."
Yes, I meant "index returns only a small percentage", but query selects
is better. Thanks.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index doesn't appear to be working.
@ 2002-03-03 18:41 Frank Bax <fbax@sympatico.ca>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 1 reply; 7+ messages in thread
From: Frank Bax @ 2002-03-03 18:41 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-sql
At 11:02 AM 03/03/02 -0500, Bruce Momjian wrote:
> Indexes are not automatically used by every query. Indexes are only
> used if the table is larger than a minimum size, and the index
> selects only a small percentage of the rows in the table.
Shouldn't it say:
"and the *query* selects only a small percentage..."
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-03-03 18:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-03 00:28 Index doesn't appear to be working. John Oakes <john@networkproductions.net>
2002-03-03 07:50 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-03 16:02 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-03-03 16:58 ` Wei Weng <wweng@kencast.com>
2002-03-03 18:26 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-03-03 18:41 ` Frank Bax <fbax@sympatico.ca>
2002-03-03 18:36 ` Bruce Momjian <pgman@candle.pha.pa.us>
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