Re: PG does not use my index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 4/2/07, Rickard Sjöström <rickard.sjostrom@xxxxxxxxxxxxxx> wrote:
Hi!
This post is related to the post "Performance of views" but this is another problem now.

Problem: PostgreSQL seems to not use my index.

My postgres is 7.4.

I started all over again and got my query really fast on one database (~40 seconds became 150 ms). BUT when trying on another db (same postgres server but with approx. 4 times as much data) it was really slow again (say 10 seconds).

Then I run the EXPLAIN ANALYSE of a sub-query of my query which I realize was the problem.

It seems that it does not make use of the index in the slower database!?

fast db:
-------------------------
 ->  Index Scan using testcase_b_bid_index on testcase  (cost=0.00..1656.82 rows=426 width=38) (never executed)"
-------------------------


slow db:
-------------------------
->  Seq Scan on testcase  (cost=0.00..2896.42 rows=33242 width=64) (actual time=77.027..791.014 rows=37093 loops=1)"
-------------------------

rows=37093 is all of the existing rows of table testcase!
(why does it say "never executed"?)

Hi,
a full index scan is even more expensive than a sequential scan.
So the query optimizer works fine.

Regards
Federico


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux