472,352 Members | 1,483 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,352 software developers and data experts.

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:

SET enable_seqscan = off;
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;
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
-> 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
Filter: (name = 'Rudy'::character
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #1
1 1809
> 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 ?

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 ma*******@postgresql.org)

Nov 11 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database...
by: Joshua D. Drake | last post by:
Hello, I think the below just about says it all: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg Sincerely, Joshua...
by: John Wells | last post by:
Yes, I know you've seen the above subject before, so please be gentle with the flamethrowers. I'm preparing to enter a discussion with management...
by: Marc G. Fournier | last post by:
After almost 12 months of intense development, and testing, we are proud to announce the availability of PostgreSQL v7.4. An overview of the...
by: Marek Lewczuk | last post by:
Hello, I have changed DB from MySQL to PostgreSQL. When I have run my application on PostgreSQL it was disaster - it was much slower than MySQL......
by: Paul Ganainm | last post by:
Hi all, Following up on another thread, here is a comparison between FB and PG from an FB'ers POV. BTW, FB is the love-child of...
by: Jean-Michel POURE | last post by:
Dear friends, I am currently testing Ulogd ip traffic logging system with PostgreSQL. It works in conjunction with GNU/Linux iptables. The...
by: Sezai YILMAZ | last post by:
Hello I need high throughput while inserting into PostgreSQL. Because of that I did some PostgreSQL insert performance tests. ...
by: Jim Strickland | last post by:
We currently are running a data intensive web service on a Mac using 4D. The developers of our site are looking at converting this web service to...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.