Horton

Thread

  1. Can't get Postgres to use indices

    Othman Laraki <othman@epitrope.com> — 2001-05-04T03:54:14Z

    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

  2. Can't get Postgres to use indices

    Othman Laraki <othman@epitrope.com> — 2001-05-04T04:00:05Z

    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

  3. Re: Can't get Postgres to use indices

    Mitch Vincent <mitch@venux.net> — 2001-05-04T04:00:08Z

        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
>

  4. RE: Can't get Postgres to use indices

    Othman Laraki <othman@epitrope.com> — 2001-05-04T04:17:25Z

    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
>

  5. Re: RE: Can't get Postgres to use indices

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-04T04:47:52Z

    "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

  6. RE: RE: Can't get Postgres to use indices

    Othman Laraki <othman@epitrope.com> — 2001-05-04T17:46:26Z

    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