Query plan not using index for some reason.

From: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Query plan not using index for some reason.
Date: 2002-10-01 15:56:10
Message-ID: 3D99C59A.6090503@mega-bucks.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

One of my SQL is is slow so I tried using EXPLAIN to find out why but
the query plan is gives seems bad ... it's not using indexes ...

The query is on two tables, both of which have indexes. When I check
EXPLAIN for the query without the OR clause the planner uses the index.
When I add the OR clause it uses a seq scan ...

Is the planner right in choosing a seq scan?

Here is the relevant data:

$ psql TMP -c "vacuum analyze"
VACUUM

$ psql TMP -c "explain select products.id as pid from
products,rel_genres_movies where maker_id='53' OR
(rel_genres_movies.minor_id='11' AND rel_genres_movies.prod_id=products.id)"
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..8906651.40 rows=2677 width=40)
-> Seq Scan on products (cost=0.00..953.85 rows=14285 width=20)
-> Seq Scan on rel_genres_movies (cost=0.00..289.81 rows=16681
width=20)

EXPLAIN

#BUT ... removing either side of the OR clause gives an index scan ...

$ psql TMP -c "explain select products.id as pid from
products,rel_genres_movies where (rel_genres_movies.minor_id='11' AND
rel_genres_movies.prod_id=products.id)"
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..975.45 rows=145 width=32)
-> Seq Scan on rel_genres_movies (cost=0.00..331.51 rows=145 width=16)
-> Index Scan using products_pkey on products (cost=0.00..4.43
rows=1 width=16)

EXPLAIN

$ psql TMP -c "explain select products.id as pid from
products,rel_genres_movies where maker_id='53'"
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..7100.10 rows=257505 width=16)
-> Index Scan using idx_products_maker_id on products
(cost=0.00..51.25 rows=15 width=16)
-> Seq Scan on rel_genres_movies (cost=0.00..289.81 rows=16681 width=0)

EXPLAIN

Jc

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2002-10-01 16:25:39 Re: Query plan not using index for some reason.
Previous Message Chris Gamache 2002-10-01 15:27:31 COPY FROM stdin;