pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alexaki Sofia <alexaki@ics.forth.gr>
To: pgsql-sql@postgresql.org
Subject: Re: A question about indexes...
Date: Fri, 4 Aug 2000 13:02:47 +0300 (EET DST)
Message-ID: <Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr> (raw)


Hello,

I have the following  tables in my database
Painter(id integer, uri varchar(256))
paints(id1 integer, id2 integer)

in order to speed up the join (select * from painter, paints where
painter.id= paints.id1)  between these two tables I have created indexes
on the field painter.id and/or paints.id1.

But as I see from the query plan the indexes are not used, instead
sequential search  is done either I define indexes or not.
As you can see below the query plan remains the same.
Is that reasonable??? Shouldn't Postgresql use the indexes in order 
to optimize question???I can't see why is better to make sequential search
since the size of tables is relatively big.


A)  No indexes are defined on the tables 
Hash Join  (cost=12269.78 rows=60014 width=24)
  ->  Seq Scan on painter1  (cost=4234.97 rows=99999 width=16)
  ->  Hash  (cost=1931.92 rows=50331 width=8)
        ->  Seq Scan on paints  (cost=1931.92 rows=50331 width=8)
 
B1)
BTree index on painter.id
 
Hash Join  (cost=12269.78 rows=60014 width=24)
  ->  Seq Scan on painter  (cost=4234.97 rows=99999 width=16)
  ->  Hash  (cost=1931.92 rows=50331 width=8)
        ->  Seq Scan on paints  (cost=1931.92 rows=50331 width=8)

B2) 
Primary Key on painter.id
Hash Join  (cost=12269.78 rows=60014 width=24)
  ->  Seq Scan on painter  (cost=4234.97 rows=99999 width=16)
  ->  Hash  (cost=1931.92 rows=50331 width=8)
        ->  Seq Scan on paints  (cost=1931.92 rows=50331 width=8)
 
C1)
BTree index on painter.id and Btree on paints.id1
Hash Join  (cost=12269.78 rows=60014 width=24)
  ->  Seq Scan on painter  (cost=4234.97 rows=99999 width=16)
  ->  Hash  (cost=1931.92 rows=50331 width=8)
        ->  Seq Scan on paints  (cost=1931.92 rows=50331 width=8)

C2)
Primary Key on painter.id and Btree on paints.id1
Hash Join  (cost=12269.78 rows=60014 width=24)
  ->  Seq Scan on painter  (cost=4234.97 rows=99999 width=16)
  ->  Hash  (cost=1931.92 rows=50331 width=8)
        ->  Seq Scan on paints  (cost=1931.92 rows=50331 width=8)

Regards,
Sofia Alexaki





view thread (4+ messages)  latest in thread

Message-ID: <Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr>
Permalink:  ../Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr/
Also on:    postgresql.org/message-id/Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: alexaki@ics.forth.gr
  Subject: Re: A question about indexes...
  In-Reply-To: <Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr>
  References: <Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox