SEO

Good day,

I have noticed that my server never uses indexing. No matter what I do.

As an example I took a table with about 650 rows, having a parentid
field with an index on parentid.

EXPLAIN ANALYZE
SELECT *
FROM layertype
where parentid = 300;

On my laptop the explain analyze looks like this:

"Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27
rows=1 width=109)"
" Index Cond: (parentid = 300)"

and on the problem server:

"Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)"
" Filter: (parentid = 300)"

..........

I have dropped the index, recreated it, vacuumed the table, just about
everything I could think of, And there is just no way I can get the
query planner to use the index.

PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20061115 (prerelease) (SUSE Linux)
POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct
2006" USE_STATS


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

try it with a table with 650K rows...

On Tue, 2007-06-12 at 15:32 +0200, Christo Du Preez wrote:
> Good day,
>
> I have noticed that my server never uses indexing. No matter what I do.
>
> As an example I took a table with about 650 rows, having a parentid
> field with an index on parentid.
>
> EXPLAIN ANALYZE
> SELECT *
> FROM layertype
> where parentid = 300;
>
> On my laptop the explain analyze looks like this:
>
> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27
> rows=1 width=109)"
> " Index Cond: (parentid = 300)"
>
> and on the problem server:
>
> "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)"
> " Filter: (parentid = 300)"
>
> .........
>
> I have dropped the index, recreated it, vacuumed the table, just about
> everything I could think of, And there is just no way I can get the
> query planner to use the index.
>
> PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
> 20061115 (prerelease) (SUSE Linux)
> POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct
> 2006" USE_STATS
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Jun 12, 2007, at 8:32 , Christo Du Preez wrote:

> I have noticed that my server never uses indexing. No matter what I
> do.
>
> As an example I took a table with about 650 rows, having a parentid
> field with an index on parentid.
>
> EXPLAIN ANALYZE
> SELECT *
> FROM layertype
> where parentid = 300;

The planner weighs the cost of the different access methods and
choses the one that it believes is lowest in cost. An index scan is
not always faster than a sequential scan. With so few rows, it's
probably faster for the server to read the whole table rather than
reading the index and looking up the corresponding row. If you want
to test this, you can set enable_seqscan to false and try running
your query again.

http://www.postgresql.org/docs/8.2/i...untime-config-
query.html#RUNTIME-CONFIG-QUERY-ENABLE

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"Christo Du Preez" <christo@mecola.com> writes:

> On my laptop the explain analyze looks like this:
>
> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27
> rows=1 width=109)"
> " Index Cond: (parentid = 300)"

That's not "explain analyze", that's just plain "explain".

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

The actual table I noticed the problem has a million rows and it still
doesn't use indexing

Reid Thompson wrote:
> try it with a table with 650K rows...
>
> On Tue, 2007-06-12 at 15:32 +0200, Christo Du Preez wrote:
>
>> Good day,
>>
>> I have noticed that my server never uses indexing. No matter what I do.
>>
>> As an example I took a table with about 650 rows, having a parentid
>> field with an index on parentid.
>>
>> EXPLAIN ANALYZE
>> SELECT *
>> FROM layertype
>> where parentid = 300;
>>
>> On my laptop the explain analyze looks like this:
>>
>> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27
>> rows=1 width=109)"
>> " Index Cond: (parentid = 300)"
>>
>> and on the problem server:
>>
>> "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)"
>> " Filter: (parentid = 300)"
>>
>> .........
>>
>> I have dropped the index, recreated it, vacuumed the table, just about
>> everything I could think of, And there is just no way I can get the
>> query planner to use the index.
>>
>> PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
>> 20061115 (prerelease) (SUSE Linux)
>> POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct
>> 2006" USE_STATS
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>
>
>

--
Christo Du Preez

Senior Software Engineer
Mecola IT
Mobile: +27 [0]83 326 8087
Skype: christodupreez
Website: http://www.locateandtrade.co.za


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Christo Du Preez wrote:
> The actual table I noticed the problem has a million rows and it still
> doesn't use indexing

So ANALYZE it.

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"Amanece. (Ignacio Reyes)
El Cerro San Cristóbal me mira, cínicamente, con ojos de virgen"

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Tue, Jun 12, 2007 at 03:32:40PM +0200, Christo Du Preez wrote:
> As an example I took a table with about 650 rows, having a parentid
> field with an index on parentid.

Try a bigger table. Using an index for only 650 rows is almost always
suboptimal, so it's no wonder the planner doesn't use the index.

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, Jun 12, 2007 at 04:11:33PM +0200, Christo Du Preez wrote:
> The actual table I noticed the problem has a million rows and it still
> doesn't use indexing

Then please post an EXPLAIN ANALYZE of the query that is slow, along with the
table definition and indexes.

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Christo Du Preez <christo@mecola.com> writes:
> On my laptop the explain analyze looks like this:

> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27
> rows=1 width=109)"
> " Index Cond: (parentid = 300)"

OK ...

> and on the problem server:

> "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)"
> " Filter: (parentid = 300)"

The server thinks that every row of the table matches the WHERE clause.
That being the case, it's making the right choice to use a seqscan.
The question is why is the rows estimate so far off? Have you ANALYZEd
the table lately?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Yes, I have just about tried every combination of vacuum on the
database. Just to make 100% sure.

Tom Lane wrote:
> Christo Du Preez <christo@mecola.com> writes:
>
>> On my laptop the explain analyze looks like this:
>>
>
>
>> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27
>> rows=1 width=109)"
>> " Index Cond: (parentid = 300)"
>>
>
> OK ...
>
>
>> and on the problem server:
>>
>
>
>> "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)"
>> " Filter: (parentid = 300)"
>>
>
> The server thinks that every row of the table matches the WHERE clause.
> That being the case, it's making the right choice to use a seqscan.
> The question is why is the rows estimate so far off? Have you ANALYZEd
> the table lately?
>
> regards, tom lane
>
>
>

--
Christo Du Preez

Senior Software Engineer
Mecola IT
Mobile: +27 [0]83 326 8087
Skype: christodupreez
Website: http://www.locateandtrade.co.za


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings