Skip site navigation (1) Skip section navigation (2)

seq scan instead of index scan

From: Karl Larsson <karl(dot)larsson47(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: seq scan instead of index scan
Date: 2009-12-17 23:22:15
Message-ID: d7650d320912171522w7225b3f4vd2ea9279a35f86c8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello.

I have a problem I don't understand. I hope it's a simple problem and I'm
just stupid.

When I make a subquery Postgres don't care about my indexes and makes
a seq scan instead of a index scan. Why?

Is it possible that the subquery change the datatype and by this make
a index scan impossible? Can I somehow see the datatypes used by the
query?

Below is the test I'm running.

/ Karl Larsson


CREATE TABLE table_one (
    id bigint PRIMARY KEY NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"table_one_pkey" for table "table_one"

CREATE TABLE table_two (
    id bigint PRIMARY KEY NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"table_two_pkey" for table "table_two"




INSERT INTO table_one VALUES (4);
INSERT INTO table_one VALUES (3);
INSERT INTO table_one VALUES (5);
INSERT INTO table_one VALUES (2);
INSERT INTO table_one VALUES (6);
INSERT INTO table_one VALUES (1);

INSERT INTO table_two VALUES (14);
INSERT INTO table_two VALUES (12);
INSERT INTO table_two VALUES (10);
INSERT INTO table_two VALUES (8);
INSERT INTO table_two VALUES (6);
INSERT INTO table_two VALUES (4);
INSERT INTO table_two VALUES (2);



EXPLAIN ANALYZE
SELECT t2.id
FROM table_two AS t2, (
    SELECT id
    FROM table_one AS t1
    WHERE t1.id < 6
  ) AS foo
WHERE t2.id = foo.id;




                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=35.44..78.58 rows=647 width=8) (actual time=0.076..0.088
rows=2 loops=1)
   Hash Cond: (t2.id = t1.id)
   ->  Seq Scan on table_two t2  (cost=0.00..29.40 rows=1940 width=8)
(actual time=0.007..0.021 rows=7 loops=1)
   ->  Hash  (cost=27.35..27.35 rows=647 width=8) (actual time=0.038..0.038
rows=5 loops=1)
         ->  Bitmap Heap Scan on table_one t1  (cost=9.26..27.35 rows=647
width=8) (actual time=0.014..0.022 rows=5 loops=1)
               Recheck Cond: (id < 6)
               ->  Bitmap Index Scan on table_one_pkey  (cost=0.00..9.10
rows=647 width=0) (actual time=0.008..0.008 rows=5 loops=1)
                     Index Cond: (id < 6)
 Total runtime: 0.133 ms

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-12-17 23:26:36
Subject: Re: seq scan instead of index scan
Previous:From: Grzegorz JaƛkiewiczDate: 2009-12-17 20:05:28
Subject: Re: Automatic optimization of IN clauses via INNER JOIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group