RE: Can't get Postgres to use indices - Mailing list pgsql-general

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@venux.net]
Sent: Thursday, May 03, 2001 9:00 PM
To: Othman Laraki; pgsql-general@postgresql.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@epitrope.com>
To: <pgsql-general@postgresql.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@epitrope.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>