RE: Can't get Postgres to use indices
От | Othman Laraki |
---|---|
Тема | RE: Can't get Postgres to use indices |
Дата | |
Msg-id | GBEGLFJBEMGPPFJEFJNAAEJBCJAA.othman@epitrope.com обсуждение исходный текст |
Ответ на | Re: Can't get Postgres to use indices ("Mitch Vincent" <mitch@venux.net>) |
Ответы |
Re: RE: Can't get Postgres to use indices
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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 >
В списке pgsql-general по дате отправления: