RE: Can't get Postgres to use indices

Thanks for the quick response. The info is below:

CREATING THE TABLES
===================

CREATE TABLE ep_tbl_page_topic_map (
tid INT8 NOT NULL,
pid INT8 NOT NULL,
weight INTEGER NULL,
PRIMARY KEY (tid, pid)
);

CREATE INDEX ep_index_page_topic_map_page on ep_tbl_page_topic_map (pid);

CREATE TABLE ep_tbl_cache(
x1 VARCHAR(100) NULL,
x2 VARCHAR(100) NULL,
x3 VARCHAR(100) NULL,
x4 VARCHAR(100) NULL,
x5 VARCHAR(100) NULL,
identifier VARCHAR(255) NULL,
time_modified TIMESTAMP NULL DEFAULT current_timestamp
);

CONTENTS
========

ep_tbl_cache has 16557 rows
ep_tbl_page_topic_map has 5541 rows

THE QUERY
=========

SELECT a1.tid, x2 FROM ep_tbl_cache, ep_tbl_page_topic_map as a1 WHERE
x1=a1.pid AND x1 is not null and x1 <> '' and x1 <> '-' AND
ep_tbl_cache.identifier = 'bg2hyr0p51_cached_Thu May 03 13:43:07 PDT 2001';

THE EXPLAIN
===========
Epi=# explain SELECT a1.tid, x2 FROM ep_tbl_cache, ep_tbl_page_topic_map as
a1 WHERE x1=a1.pid AND x1 is not null and x1 <> '' and x1 <> '-' AND
ep_tbl_cache.identifier = 'bg2hyr0p51_cached_Thu May 03 13:43:07 PDT 2001';
NOTICE: QUERY PLAN:

Nested Loop (cost=200000000.00..200033366.82 rows=37 width=40)
-> Seq Scan on ep_tbl_cache (cost=100000000.00..100000579.14 rows=185
width=24)
-> Seq Scan on ep_tbl_page_topic_map a1 (cost=100000000.00..100000094.41
rows=5541 width=16)

-----Original Message-----
From: Mitch Vincent [mailto:mitch(at)venux(dot)net]
Sent: Thursday, May 03, 2001 9:00 PM
To: Othman Laraki; pgsql-general(at)postgresql(dot)org
Subject: Re: Can't get Postgres to use indices

Can we see the schema of the tables and the queries that don't use the
indexes along with an EXPLAIN of those queries?

-Mitch

----- Original Message -----
From: "Othman Laraki" <othman(at)epitrope(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Friday, May 04, 2001 12:00 AM
Subject: Can't get Postgres to use indices

>
> Hi, I have been having problems getting Postgres to use indexes in
queries.
> I read as many postings and FAQs as I could find, but to no avail.
>
> I installed Postgres 7.1 on a Linux machine with ample memory and followed
> very standard installation procedures with most default options - the only
> difference is that I installed it with the 'enable-locale' and
> 'enable-multibyte' options turned on. I have many different types of
tables,
> with indexes, as well as primary keys all over the place. The DB is also
> properly vacuumed, so the indexes should be up to date. However, none of
the
> queries that I run ever use indexes. I even set 'ENABLE_SEQSCAN' to off in
> postgresql.conf, but Postgres still uses sequential scans for everything,
> even on a join on indexed columns of two tables that have 10,000+ rows.
>
> Any suggestions would be much appreciated!
>
> Thanks,
> Othman Laraki
>
> _____________________________
> Othman Laraki
> Epitrope Corporation
> 860 Hillview Court, Suite 200
> Milpitas, CA 95035, USA
> 650-814-9580 (phone)
> 650-745-2449 (fax)
> othman(at)epitrope(dot)com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>