Re: [PERFORM] PostgreSQL caching

[Vitaly Belman]

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: [EMAIL PROTECTED]
 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 (
MSSELECT book_id
MSFROM bv_genres
MSWHERE 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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] tuning for AIX 5L with large memory

[Dan Harris]

Christopher Browne wrote:
We have a couple of these at work; they're nice and fast, although the
process of compiling things, well, makes me feel a little unclean.
 

Thanks very much for your detailed reply, Christopher.  Would you mind 
elaborating on the makes me feel a little unclean statement? Also, I'm 
curious which models you are running and if you have any anecdotal 
comparisons for perfomance?  I'm completely unfamiliar with AIX, so if 
there are dark corners that await me, I'd love to hear a little more so 
I can be prepared.  I'm going out on a limb here and jumping to an 
unfamiliar architecture as well as OS, but the IO performance of these 
systems has convinced me that it's what I need to break out of my I/O 
limited x86 systems.

I suppose when I do get it, I'll just experiment with different sizes of 
shared memory and run some benchmarks.  For the price of these things, 
they better be some good marks!

Thanks again
-Dan Harris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster