Re: Performance on views

Hi!
I've probably missed somthing but here is my problem.

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.

Example:
--------------------------------------------
>From view, the slow one:
SELECT * from my_view WHERE date > 2007-03-01

and to speed it up I just copy the view defintion and inserts some "date > ...", like this.

SELECT *
FROM (select * from my_table where data > 2007-03-01) mt,
my JOIN someother on mt.id=smoeother.id
etc,...
--------------------------------------------

The thing here is that I limit the query before joining with other data.

Did I get through with my problem?
Any ideas how I can speed up my views?
Is there something I can tell the database in order to speed up?
Is this a known issue with views?

/Rickard

----------------------------------------------------------------------
Click to lower your debt and consolidate your monthly expenses
http://tags.bluebottle.com/fc/CAaCMPJklAkSFsDVLmOtm1fwWle86ZFg/

Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?= <rickard(dot)sjostrom(at)bluebottle(dot)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

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(at)sss(dot)pgh(dot)pa(dot)us>:

> Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?=
> <rickard(dot)sjostrom(at)bluebottle(dot)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/

On 4/2/07, Rickard Sjöström <rickard(dot)sjostrom(at)bluebottle(dot)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

Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?= <rickard(dot)sjostrom(at)bluebottle(dot)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

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(at)sss(dot)pgh(dot)pa(dot)us>:

> Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?=
> <rickard(dot)sjostrom(at)bluebottle(dot)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
>