pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPG does not use my index
4+ messages / 3 participants
[nested] [flat]
* PG does not use my index
@ 2007-04-02 11:26 Rickard Sjöström <rickard.sjostrom@bluebottle.com>
2007-04-02 15:08 ` Re: PG does not use my index Federico <rotellaro@gmail.com>
2007-04-02 16:05 ` Re: PG does not use my index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Rickard Sjöström @ 2007-04-02 11:26 UTC (permalink / raw)
To: pgsql-admin
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"?)
Any ideas why it does not use my index??? How can I convince it to do so?
/Rickard
Citerar Tom Lane <tgl@sss.pgh.pa.us>:
> Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?=
> <rickard.sjostrom@bluebottle.com> writes:
> > I have a view that is really slow and I ca easily work around the
> slowness by bypassing the view and query the real table directly.
>
> Let's see the exact definition of the view and EXPLAIN ANALYZE
> results
> for doing it both ways. Also, what PG version is this?
>
> regards, tom lane
>
----------------------------------------------------------------------
Need cash? Click to get an emergency loan, bad credit ok
http://tags.bluebottle.com/fc/CAaCMPJe8z3dVmZgRfajsIJMkopwDwDI/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PG does not use my index
2007-04-02 11:26 PG does not use my index Rickard Sjöström <rickard.sjostrom@bluebottle.com>
@ 2007-04-02 15:08 ` Federico <rotellaro@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Federico @ 2007-04-02 15:08 UTC (permalink / raw)
To: Rickard Sjöström <rickard.sjostrom@bluebottle.com>; +Cc: pgsql-admin
On 4/2/07, Rickard Sjöström <rickard.sjostrom@bluebottle.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PG does not use my index
2007-04-02 11:26 PG does not use my index Rickard Sjöström <rickard.sjostrom@bluebottle.com>
@ 2007-04-02 16:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-03 05:39 ` Re: PG does not use my index Rickard Sjöström <rickard.sjostrom@bluebottle.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2007-04-02 16:05 UTC (permalink / raw)
To: Rickard Sjöström <rickard.sjostrom@bluebottle.com>; +Cc: pgsql-admin
Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?= <rickard.sjostrom@bluebottle.com> writes:
> 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)"
> -------------------------
Let's see the query and the *whole* explain analyze output, not the part
you (mistakenly) think is important.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PG does not use my index
2007-04-02 11:26 PG does not use my index Rickard Sjöström <rickard.sjostrom@bluebottle.com>
2007-04-02 16:05 ` Re: PG does not use my index Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-03 05:39 ` Rickard Sjöström <rickard.sjostrom@bluebottle.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rickard Sjöström @ 2007-04-03 05:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi!
I eventually tried with an 'vacuum analyse' which made postgres to use my index and the query was completed in 200 ms (insted of tens of seconds) and I was happy again!
Thanks!
/Rickard
Citerar Tom Lane <tgl@sss.pgh.pa.us>:
> Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?=
> <rickard.sjostrom@bluebottle.com> writes:
> > 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)"
> > -------------------------
>
> Let's see the query and the *whole* explain analyze output, not the
> part
> you (mistakenly) think is important.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-04-03 05:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-02 11:26 PG does not use my index Rickard Sjöström <rickard.sjostrom@bluebottle.com>
2007-04-02 15:08 ` Federico <rotellaro@gmail.com>
2007-04-02 16:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-03 05:39 ` Rickard Sjöström <rickard.sjostrom@bluebottle.com>
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