Postgres Pro
Downloads
Home   >   mailing lists

Thread: postgresql not using index even though it's faster

postgresql not using index even though it's faster

From
Rudy Koento
Date:
Hi, I've created an index but it's not being used by
postgresql when doing a query.  But doing an "explain
analyze" shows that with index, it's faster.  Here's
the output:

------------------------
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT S.* FROM sales S, staff ST
WHERE S.staff_no=ST.staff_no AND ST.name='Rudy';

Nested Loop  (cost=0.00..351.35 rows=808 width=51)
(actual time=0.39..11.82 rows=717 loops=1)
   ->  Index Scan using staff_pkey on staff st
(cost=0.00..5.86 rows=1 width=4) (actual
time=0.19..0.24 rows=1 loops=1)
         Filter: (name = 'Rudy'::character varying)
   ->  Index Scan using sales_staff_no_idx on sales s
(cost=0.00..332.02 rows=1077 width=47) (actual
time=0.19..8.22 rows=717 loops=1)
         Index Cond: (s.staff_no = "outer".staff_no)
 Total runtime: 12.60 msec
(6 rows)

------------------------
SET enable_seqscan = on;
EXPLAIN ANALYZE SELECT S.* FROM sales S, staff ST
WHERE S.staff_no=ST.staff_no AND ST.name='Rudy';

Hash Join  (cost=1.15..253.60 rows=808 width=51)
(actual time=0.30..64.83 rows=717 loops=1)
   Hash Cond: ("outer".staff_no = "inner".staff_no)
   ->  Seq Scan on sales s  (cost=0.00..193.90
rows=9690 width=47) (actual time=0.06..49.63 rows=9690
loops=1)
   ->  Hash  (cost=1.15..1.15 rows=1 width=4) (actual
time=0.19..0.19 rows=0 loops=1)
         ->  Seq Scan on staff st  (cost=0.00..1.15
rows=1 width=4) (actual time=0.18..0.18 rows=1
loops=1)
               Filter: (name = 'Rudy'::character
varying)
 Total runtime: 65.47 msec
(7 rows)

I admit that I don't really understand the output of
EXPLAIN, but it's rather obvious from the above result
that an index scan is faster?

Can anyone help me?

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

Re: postgresql not using index even though it's faster

From
Karsten Hilbert
Date:
> Hi, I've created an index but it's not being used by
> postgresql when doing a query.  But doing an "explain
> analyze" shows that with index, it's faster.  Here's
> the output:

This sounds like someone needs to put a big fat link to
this FAQ item at the top of the PostgreSQL frontpage URL ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: postgresql not using index even though it's faster

From
Ron Johnson
Date:
On Tue, 2003-08-26 at 22:32, Rudy Koento wrote:
> Hi, I've created an index but it's not being used by
> postgresql when doing a query.  But doing an "explain
> analyze" shows that with index, it's faster.  Here's
> the output:
[snip]
EXPLAIN ANALYZE SELECT S.* FROM sales S, staff ST
WHERE S.staff_no=ST.staff_no AND ST.name='Rudy';
[snip]

Are S.staff_no and ST.staff_no the same data type?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"For me and windows it became a matter of easy to start with,
and becoming increasingly difficult to be productive as time
went on, and if something went wrong very difficult to fix,
compared to linux's large over head setting up and learning the
system with ease of use and the increase in productivity
becoming larger the longer I use the system."
Rohan Nicholls , The Netherlands

Re: postgresql not using index even though it's faster

From
"Maksim Likharev"
Date:
You mean
"My queries are slow or don't make use of the indexes. Why?"

may be better just fix that stone age requirement?

It just ridicules,
could statistic be build during index build, if no statistic available?


-----Original Message-----
From: Karsten Hilbert [mailto:Karsten.Hilbert@gmx.net]
Sent: Wednesday, August 27, 2003 1:52 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql not using index even though it's
faster


> Hi, I've created an index but it's not being used by
> postgresql when doing a query.  But doing an "explain
> analyze" shows that with index, it's faster.  Here's
> the output:

This sounds like someone needs to put a big fat link to
this FAQ item at the top of the PostgreSQL frontpage URL ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)