Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TfxfK-0006Wr-EB for pgsql-performance@arkaria.postgresql.org; Tue, 04 Dec 2012 18:56:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TfxfI-0003t4-Pt for pgsql-performance@arkaria.postgresql.org; Tue, 04 Dec 2012 18:56:20 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tfxf6-0003hh-73 for pgsql-performance@postgresql.org; Tue, 04 Dec 2012 18:56:08 +0000 Received: from li188-192.members.linode.com ([178.79.132.192] helo=foo.me.uk) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tfxf3-0002iU-JO for pgsql-performance@postgresql.org; Tue, 04 Dec 2012 18:56:07 +0000 Received: from webmail.cantabcapital.com ([212.44.17.222] helo=CCPPRT350008) by foo.me.uk with esmtpa (Exim 4.71) (envelope-from ) id 1TfxyU-0001Kz-64 for pgsql-performance@postgresql.org; Tue, 04 Dec 2012 19:16:11 +0000 From: To: "'postgres performance list'" References: <092a01cdd230$ff6143c0$fe23cb40$@foo.me.uk> <096f01cdd247$722264a0$56672de0$@foo.me.uk> In-Reply-To: Date: Tue, 4 Dec 2012 18:56:04 -0000 Message-ID: <099801cdd251$02c7b9c0$08572d40$@foo.me.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0999_01CDD251.02C87D10" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQE7bKfasxp9xMk0I5yxURpgMQ+bBAG7NE8VAUNOjpICTD/jZgIPmBGyAkLZdIyY4N8BkA== Content-Language: en-gb X-SA-Exim-Connect-IP: 212.44.17.222 X-SA-Exim-Mail-From: pscott@foo.me.uk X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on foo.me.uk X-Spam-Level: X-Spam-Status: No, score=-2.9 required=5.0 tests=ALL_TRUSTED,BAYES_00, HTML_MESSAGE autolearn=ham version=3.3.1 Subject: Re: Slow query: bitmap scan troubles X-SA-Exim-Version: 4.2.1 (built Sat, 07 Nov 2009 20:03:47 +0000) X-SA-Exim-Scanned: Yes (on foo.me.uk) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0999_01CDD251.02C87D10 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Ah okay, thanks. I knew I could set various things but not effective_work_mem (I tried reloading the edited config file but it didn't seem to pick it up) From: Vitalii Tymchyshyn [mailto:tivv00@gmail.com] Sent: 04 December 2012 18:51 To: postgresql@foo.me.uk Cc: postgres performance list Subject: Re: [PERFORM] Slow query: bitmap scan troubles Well, you don't need to put anything down. Most settings that change planner decisions can be tuned on per-quey basis by issuing set commands in given session. This should not affect other queries more than it is needed to run query in the way planner chooses. Best regards, Vitalii Tymchyshyn 2012/12/4 >> But the row estimates are not precise at the top of the join/filter. >> It thinks there will 2120 rows, but there are only 11. >Ah... I didn't spot that one... Yes, you are right there - this is probably a slightly atypical query of this sort actually, 2012 is a pretty good guess. On Claudio's suggestion I have found lots more things to read up on and am eagerly awaiting 6pm when I can bring the DB down and start tweaking. The effective_work_mem setting is going from 6Gb->88Gb which I think will make quite a difference. I still can't quite wrap around my head why accessing an index is expected to use more disk access than doing a bitmap scan of the table itself, but I guess it does make a bit of sense if postgres assumes the table is more likely to be cached. It's all quite, quite fascinating :) I'll let you know how it goes. - Phil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Best regards, Vitalii Tymchyshyn ------=_NextPart_000_0999_01CDD251.02C87D10 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Ah okay, thanks. I knew I could set various things but not = effective_work_mem (I tried reloading the edited config file but it = didn’t seem to pick it up)

 

 

From:= Vitalii = Tymchyshyn [mailto:tivv00@gmail.com]
Sent: 04 December 2012 = 18:51
To: postgresql@foo.me.uk
Cc: postgres = performance list
Subject: Re: [PERFORM] Slow query: bitmap = scan troubles

 

Well, you = don't need to put anything down. Most settings that change planner = decisions can be tuned on per-quey basis by issuing set commands in = given session. This should not affect other queries more than it is = needed to run query in the way planner chooses.

 

Best regards, Vitalii = Tymchyshyn

 

2012/12/4 <postgresql@foo.me.uk>


>> But the = row estimates are not precise at the top of the join/filter.
>> = It thinks there will 2120 rows, but there are only 11.

>Ah... = I didn't spot that one...

Yes, = you are right there - this is probably a slightly atypical query = of
this sort actually, 2012 is a pretty good guess.

On = Claudio's suggestion I have found lots more things to read up on and = am
eagerly awaiting 6pm when I can bring the DB down and start = tweaking. The
effective_work_mem setting is going from 6Gb->88Gb = which I think will make
quite a difference.

I still can't = quite wrap around my head why accessing an index is expected
to use = more disk access than doing a bitmap scan of the table itself, but = I
guess it does make a bit of sense if postgres assumes the table is = more
likely to be cached.

It's all quite, quite fascinating = :)

I'll let you know how it goes.

- = Phil




--
Sent = via pgsql-performance mailing list (pgsql-performance@postgr= esql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



-- =
Best regards,
 Vitalii = Tymchyshyn

------=_NextPart_000_0999_01CDD251.02C87D10--