Optimising a query requiring seqscans=0

On Sep 14, 2006, at 11:15 AM, Russ Brown wrote:

We recently upgraded our trac backend from sqlite to postgres, and I
decided to have a little fun and write some reports that delve into
trac's subversion cache, and got stuck with a query optimisation
problem.

Table revision contains 2800+ rows
Table node_change contains 370000+.

<...>

I've got stuck with this query:

SELECT author, COUNT(DISTINCT r.rev)
FROM revision AS r
LEFT JOIN node_change AS nc
ON r.rev=nc.rev
WHERE r.time >= EXTRACT(epoch FROM (NOW() - interval '30
days'))::integer

Man I really hate when people store time_t in a database...

GROUP BY r.author;

Statistics are set to 20, and I have ANALYZEd both tables.

The report itself isn't important, but I'm using this as an
exercise in
PostgreSQL query optimisation and planner tuning, so any help/hints
would be appreciated.

Setting statistics higher (100-200), at least for the large table
will likely help. Also make sure that you've set effective_cache_size
correctly (I generally set it to total memory - 1G, assuming the
server has at least 4G in it).
--
Jim Nasby jimn@enterprisedb.com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Thu, 2006-09-21 at 23:39 -0400, Jim Nasby wrote:

On Sep 14, 2006, at 11:15 AM, Russ Brown wrote:

We recently upgraded our trac backend from sqlite to postgres, and I
decided to have a little fun and write some reports that delve into
trac's subversion cache, and got stuck with a query optimisation
problem.

Table revision contains 2800+ rows
Table node_change contains 370000+.

<...>

I've got stuck with this query:

SELECT author, COUNT(DISTINCT r.rev)
FROM revision AS r
LEFT JOIN node_change AS nc
ON r.rev=nc.rev
WHERE r.time >= EXTRACT(epoch FROM (NOW() - interval '30
days'))::integer

Man I really hate when people store time_t in a database...

I know. Probably something to do with database engine independence. I
don't know if sqlite even has a date type (probably does, but I haven't
checked).

GROUP BY r.author;

Statistics are set to 20, and I have ANALYZEd both tables.

The report itself isn't important, but I'm using this as an
exercise in
PostgreSQL query optimisation and planner tuning, so any help/hints
would be appreciated.

Setting statistics higher (100-200), at least for the large table
will likely help. Also make sure that you've set effective_cache_size
correctly (I generally set it to total memory - 1G, assuming the
server has at least 4G in it).

Thank you: the problem was the effective_cache_size (which I hadn't
changed from the default of 1000). This machine doesn't have loads of
RAM, but I knocked it up to 65536 and now the query uses the index,
without having to change the statistics.

Thanks a lot!

Russ Brown <pickscrape@gmail.com> writes on pgsql-general:

On Thu, 2006-09-21 at 23:39 -0400, Jim Nasby wrote:

Also make sure that you've set effective_cache_size
correctly (I generally set it to total memory - 1G, assuming the
server has at least 4G in it).

Thank you: the problem was the effective_cache_size (which I hadn't
changed from the default of 1000). This machine doesn't have loads of
RAM, but I knocked it up to 65536 and now the query uses the index,
without having to change the statistics.

Considering recent discussion about how 8.2 is probably noticeably more
sensitive to effective_cache_size than prior releases, I wonder whether
it's not time to adopt a larger default value for that setting. The
current default of 1000 pages (8Mb) seems really pretty silly for modern
machines; we could certainly set it to 10 times that without problems,
and maybe much more. Thoughts?

regards, tom lane

Thank you: the problem was the effective_cache_size (which I hadn't
changed from the default of 1000). This machine doesn't have loads of
RAM, but I knocked it up to 65536 and now the query uses the index,
without having to change the statistics.

Considering recent discussion about how 8.2 is probably noticeably more
sensitive to effective_cache_size than prior releases, I wonder whether
it's not time to adopt a larger default value for that setting. The
current default of 1000 pages (8Mb) seems really pretty silly for modern
machines; we could certainly set it to 10 times that without problems,
and maybe much more. Thoughts?

I think that 128 megs is probably a reasonable starting point. I know
plenty of people that run postgresql on 512 megs of ram. If you take
into account shared buffers and work mem, that seems like a reasonable
starting point.

Joshua D. Drake

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Sat, 2006-09-23 at 17:14 -0700, Joshua D. Drake wrote:

Thank you: the problem was the effective_cache_size (which I hadn't
changed from the default of 1000). This machine doesn't have loads of
RAM, but I knocked it up to 65536 and now the query uses the index,
without having to change the statistics.

Considering recent discussion about how 8.2 is probably noticeably more
sensitive to effective_cache_size than prior releases, I wonder whether
it's not time to adopt a larger default value for that setting. The
current default of 1000 pages (8Mb) seems really pretty silly for modern
machines; we could certainly set it to 10 times that without problems,
and maybe much more. Thoughts?

I think that 128 megs is probably a reasonable starting point. I know
plenty of people that run postgresql on 512 megs of ram. If you take
into account shared buffers and work mem, that seems like a reasonable
starting point.

I agree, Adopting a higher effective_cache_size seems to be a good thing
to do.

(hmmm.... I must be dreaming again.... But I cannot stop wondering how
it would be to have a smart "agent" that configures these values by
analyzing the machine power and statistical values gathered from
database usage......)

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Russ Brown <pickscrape@gmail.com> writes on pgsql-general:

Thank you: the problem was the effective_cache_size (which I hadn't
changed from the default of 1000). This machine doesn't have loads of
RAM, but I knocked it up to 65536 and now the query uses the index,
without having to change the statistics.

Considering recent discussion about how 8.2 is probably noticeably more
sensitive to effective_cache_size than prior releases, I wonder whether
it's not time to adopt a larger default value for that setting. The
current default of 1000 pages (8Mb) seems really pretty silly for modern
machines; we could certainly set it to 10 times that without problems,
and maybe much more. Thoughts?

I'd have to agree 100% with this. Though don't we now have something
automated for shared_buffers? I'd think effective_cache_size would
definitely be a candidate for automation (say, half or 1/4th the ram in
the box...).

Barring the ability to do something along those lines- yes, I'd
recommend up'ing it to at least 128M or 256M.

Thanks,

Stephen

current default of 1000 pages (8Mb) seems really pretty silly for modern
machines; we could certainly set it to 10 times that without problems,
and maybe much more. Thoughts?

May be, set by default effective_cache_size equal to number of shared buffers?
If pgsql is configured to use quarter or half of total memory for shared
buffer, then effective_cache_size will have good approximation...

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

Teodor Sigaev wrote:

current default of 1000 pages (8Mb) seems really pretty silly for modern
machines; we could certainly set it to 10 times that without problems,
and maybe much more. Thoughts?

May be, set by default effective_cache_size equal to number of shared
buffers?
If pgsql is configured to use quarter or half of total memory for
shared buffer, then effective_cache_size will have good approximation...

Initdb does not currently make any attempt to discover the extent of
physical or virtual memory, it simply tries to start postgres with
certain shared_buffer settings, starting at 4000, and going down until
we get a success.

max_fsm_pages is now fixed proportionally with shared_buffers, and I
guess we could do something similar with effective_cache_size, but since
IIRC this doesn't involve shared memory I'm inclined to agree with Tom
that it should just be fixed at some substantially higher level.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Initdb does not currently make any attempt to discover the extent of
physical or virtual memory, it simply tries to start postgres with
certain shared_buffer settings, starting at 4000, and going down until
we get a success.

max_fsm_pages is now fixed proportionally with shared_buffers, and I
guess we could do something similar with effective_cache_size, but since
IIRC this doesn't involve shared memory I'm inclined to agree with Tom
that it should just be fixed at some substantially higher level.

Right, the default shared_buffers doesn't have much of anything to do
with actual RAM size. If the user has altered it, then it might (or
might not) ... but that doesn't help us for setting a default
effective_cache_size.

Barring objections, I'll change it to Josh Drake's suggestion of ~ 128Mb
(versus current 8Mb).

regards, tom lane