From: | Jim Nasby <jim(at)nasby(dot)net> |
---|---|
To: | Russ Brown <pickscrape(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Optimising a query requiring seqscans=0 |
Date: | 2006-09-22 03:39:16 |
Message-ID: | 4E6B4E5B-DD9C-45DE-A915-0EADD9988E28@nasby.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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(at)enterprisedb(dot)com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2006-09-22 03:41:13 | Re: Majordomo drops multi-line Subject: |
Previous Message | Jim Nasby | 2006-09-22 03:34:58 | Re: oracle listener intercept |
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2006-09-22 03:50:20 | Re: [GENERAL] Feature request (was psql: absolutes and toggles) |
Previous Message | Jonah H. Harris | 2006-09-22 03:23:22 | Re: pg_upgrade: downgradebility |