Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL caching

From: Vitaly Belman <vitalib(at)012(dot)net(dot)il>
To: Marty Scholes <marty(at)outputservices(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL caching
Date: 2004-05-22 22:22:09
Message-ID: 188896456515.20040523012209@012.net.il (view raw or flat)
Thread:
Lists: pgsql-performance
Hello Marty,

MS> Is that a composite index?

It is a regular btree index. What is a composite index?

MS> Analyzing the taables may help, as the optimizer appears to
MS> mispredict the number of rows returned.

I'll try analyzing, but I highly doubt that it would help. I analyzed
once already and haven't changed the data since.

MS> I would be curious to see how it performs with an "IN" clause,
MS> which I would suspect would go quite a bit fasrer.

Actually it reached 20s before I canceled it... Here's the explain:

QUERY PLAN
Limit  (cost=3561.85..3561.88 rows=10 width=76)
  ->  Sort  (cost=3561.85..3562.39 rows=214 width=76)
        Sort Key: bv_books.vote_avg
        ->  Nested Loop  (cost=1760.75..3553.57 rows=214 width=76)
              ->  Index Scan using i_bookgenres_genre_id on bv_bookgenres  (cost=0.00..1681.54 rows=214 width=0)
                    Index Cond: (genre_id = 5830)
              ->  Materialize  (cost=1760.75..1761.01 rows=26 width=76)
                    ->  Nested Loop  (cost=1682.07..1760.75 rows=26 width=76)
                          ->  HashAggregate  (cost=1682.07..1682.07 rows=26 width=4)
                                ->  Index Scan using i_bookgenres_genre_id on bv_bookgenres  (cost=0.00..1681.54 rows=214 width=4)
                                      Index Cond: (genre_id = 5830)
                          ->  Index Scan using bv_books_pkey on bv_books  (cost=0.00..3.01 rows=1 width=76)
                                Index Cond: (bv_books.book_id = "outer".book_id)

                                
Thank you for your try.

Regards,
Vitaly Belman
 
 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent(at)hotmail(dot)com
 Yahoo!: VitalyBe

Friday, May 21, 2004, 11:10:56 PM, you wrote:

MS> Not knowing a whole lot about the internals of Pg, one thing jumped out
MS> at me, that each trip to get data from bv_books took 2.137 ms, which
MS> came to over 4.2 seconds right there.

MS> The problem "seems" to be the 1993 times that the nested loop spins, as
MS> almost all of the time is spent there.

MS> Personally, I am amazed that it takes 3.585 seconds to index scan 
MS> i_bookgenres_genre_id.  Is that a composite index?  Analyzing the 
MS> taables may help, as the optimizer appears to mispredict the number of
MS> rows returned.

MS> I would be curious to see how it performs with an "IN" clause, which I
MS> would suspect would go quite a bit fasrer.  Try the following:

MS> SELECT     bv_books. * ,
MS>             vote_avg,
MS>             vote_count
MS> FROM       bv_bookgenres,
MS>             bv_books
MS> WHERE      bv_books.book_id IN (
MS>                SELECT book_id
MS>                FROM bv_genres
MS>                WHERE bv_bookgenres.genre_id = 5830
MS>                )
MS> AND bv_bookgenres.genre_id = 5830
MS> ORDER BY   vote_avg DESC LIMIT 10 OFFSET 0;

MS> In this query, all of the book_id values are pulled at once.

MS> Who knows?

MS> If you get statisctics on this, please post.

MS> Marty


MS> ---------------------------(end of
MS> broadcast)---------------------------
MS> TIP 4: Don't 'kill -9' the postmaster


In response to

pgsql-performance by date

Next:From: Dan HarrisDate: 2004-05-23 03:30:25
Subject: Re: tuning for AIX 5L with large memory
Previous:From: Neil ConwayDate: 2004-05-22 02:31:15
Subject: Re: tuning for AIX 5L with large memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group