On Apr 10, 2008, at 9:44 AM, John Beaver wrote:
> Thanks a lot, all of you - this is excellent advice. With the data=20=20
> clustered and statistics at a more reasonable value of 100, it now=20=20
> reproducibly takes even less time - 20-57 ms per query.
>
> After reading the section on "Statistics Used By the Planner" in the=20=
=20
> manual, I was a little concerned that, while the statistics sped
up=20=20
> the queries that I tried immeasurably, that the most_common_vals=20=20
> array was where the speedup was happening, and that the values which=20=
=20
> wouldn't fit in this array wouldn't be sped up. Though I couldn't=20=20
> offhand find an example where this occurred, the clustering approach=20=
=20
> seems intuitively like a much more complete and scalable solution,=20=20
> at least for a read-only table like this.
>
> As to whether the entire index/table was getting into ram between my=20=
=20
> statistics calls, I don't think this was the case. Here's the=20=20
> behavior that I found:
> - With statistics at 10, the query took 25 (or so) seconds no matter=20=
=20
> how many times I tried different values. The query plan was the same=20=
=20
> as for the 200 and 800 statistics below.
> - Trying the same constant a second time gave an instantaneous=20=20
> result, I'm guessing because of query/result caching.
> - Immediately on increasing the statistics to 200, the query took
a=20=20
> reproducibly less amount of time. I tried about 10 different values
> - Immediately on increasing the statistics to 800, the query=20=20
> reproducibly took less than a second every time. I tried about 30=20=20
> different values.
> - Decreasing the statistics to 100 and running the cluster command=20=20
> brought it to 57 ms per query.
> - The Activity Monitor (OSX) lists the relevant postgres process
as=20=20
> taking a little less than 500 megs.
> - I didn't try decreasing the statistics back to 10 before I ran the=20=
=20
> cluster command, so I can't show the search times going up because=20=20
> of that. But I tried killing the 500 meg process. The new process=20=20
> uses less than 5 megs of ram, and still reproducibly returns a=20=20
> result in less than 60 ms. Again, this is with a statistics value of=20=
=20
> 100 and the data clustered by gene_prediction_view_gene_ref_key.
>
> And I'll consider the idea of using triggers with an ancillary table=20=
=20
> for other purposes; seems like it could be a useful solution for=20=20
> something.
FWIW, killing the backend process responsible for the query won't=20=20
necessarily clear the table's data from memory as that will be in
the=20=20
shared_buffers. If you really want to flush the data from memory
you=20=20
need to read in data from other tables of a size total size greater=20=20
than your shared_buffers setting.
Erik Jones
DBA | Emma=AE
erik@[EMAIL PROTECTED]
or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
|
