Thread: 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
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 >
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
>
"Othman Laraki" <othman@epitrope.com> writes:
> 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 only available join clause here is x1=a1.pid. Unfortunately,
x1 is VARCHAR(100) and a1.pid is INT8. To get a more reasonable
join plan, try fixing your table declarations so that the join
clause doesn't involve a forced type conversion.
regards, tom lane
Tom,
The ep_tbl_cache table is a temp table where temporary data of many
different types can be stored, so I don't have the option of making it an
int. However, what I did do is that I changed the join clause to
'int8(x1)=a1.pid' and that took the query from 5-minute region to below two
seconds! Thanks for the help!
-Othman
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, May 03, 2001 9:48 PM
To: Othman Laraki
Cc: Mitch Vincent; pgsql-general@postgresql.org
Subject: Re: [GENERAL] RE: Can't get Postgres to use indices
"Othman Laraki" <othman@epitrope.com> writes:
> 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 only available join clause here is x1=a1.pid. Unfortunately,
x1 is VARCHAR(100) and a1.pid is INT8. To get a more reasonable
join plan, try fixing your table declarations so that the join
clause doesn't involve a forced type conversion.
regards, tom lane