Date: Thu, 10 Apr 2008 23:37:50 +0200
To: "John Beaver" <john.e.beaver@gmail.com>, Matthew <matthew@flymine.org>
Subject: Re: large tables and simple "= constant" queries using indexes
From: PFC <lists@peufeu.com>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <47FD2DF3.80500@gmail.com> <47FD3350.4060504@tweakers.net>
	<47FD4212.8040404@gmail.com> <47FDBE23.8020402@tweakers.net>
	<op.t9dy1aqwcigqcu@apollo13.peufeu.com>
	<Pine.LNX.4.64.0804101042050.20402@aragorn.flymine.org>
	<47FE27EB.8050804@gmail.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t9ezpch9cigqcu@apollo13.peufeu.com>
In-Reply-To: <47FE27EB.8050804@gmail.com>
User-Agent: Opera Mail/9.24 (Linux)


> Thanks a lot, all of you - this is excellent advice. With the data  =

> clustered and statistics at a more reasonable value of 100, it now  =

> reproducibly takes even less time - 20-57 ms per query.

	1000x speedup with proper tuning - always impressive, lol.
	IO seeks are always your worst enemy.

> After reading the section on "Statistics Used By the Planner" in the  =

> manual, I was a little concerned that, while the statistics sped up th=
e  =

> queries that I tried immeasurably, that the most_common_vals array was=
  =

> where the speedup was happening, and that the values which wouldn't fi=
t  =

> in this array wouldn't be sped up. Though I couldn't offhand find an  =

> example where this occurred, the clustering approach seems intuitively=
  =

> like a much more complete and scalable solution, at least for a  =

> read-only table like this.

	Actually, with statistics set to 100, then 100 values will be stored in=
  =

most_common_vals. This would mean that the values not in most_common_val=
s  =

will have less than 1% frequency, and probably much less than that. The =
 =

choice of plan for these rare values is pretty simple.

	With two columns, "interesting" stuff can happen, like if you have col1=
  =

in [1...10] and col2 in [1...10] and use a condition on col1=3Dconst and=
  =

col2=3Dconst, the selectivity of the result depends not only on the  =

distribution of col1 and col2 but also their correlation.

	As for the tests you did, it's hard to say without seeing the explain  =

analyze outputs. If you change the stats and the plan choice (EXPLAIN)  =

stays the same, and you use the same values in your query, any differenc=
e  =

in timing comes from caching, since postgres is executing the same plan =
 =

and therefore doing the exact same thing. Caching (from PG and from the =
 =

OS) can make the timings vary a lot.

> - Trying the same constant a second time gave an instantaneous result,=
  =

> I'm guessing because of query/result caching.

	PG does not cache queries or results. It caches data & index pages in i=
ts  =

shared buffers, and then the OS adds another layer of the usual disk cac=
he.
	A simple query like selecting one row based on PK takes about 60  =

microseconds of CPU time, but if it needs one seek for the index and one=
  =

for the data it may take 20 ms waiting for the moving parts to move...  =

Hence, CLUSTER is a very useful tool.

	Bitmap index scans love clustered tables because all the interesting ro=
ws  =

end up being grouped together, so much less pages need to be visited.

> - I didn't try decreasing the statistics back to 10 before I ran the  =

> cluster command, so I can't show the search times going up because of =
 =

> that. But I tried killing the 500 meg process. The new process uses le=
ss  =

> than 5 megs of ram, and still reproducibly returns a result in less th=
an  =

> 60 ms. Again, this is with a statistics value of 100 and the data  =

> clustered by gene_prediction_view_gene_ref_key.

	Killing it or just restarting postgres ?
	If you let postgres run (not idle) for a while, naturally it will fill =
 =

the RAM up to the shared_buffers setting that you specified in the  =

configuration file. This is good, since grabbing data from postgres' own=
  =

cache is faster than having to make a syscall to the OS to get it from t=
he  =

OS disk cache (or disk). This isn't bloat.
	But what those 500 MB versus 6 MB show is that before, postgres had to =
 =

read a lot of data for your query, so it stayed in the cache ; after  =

tuning it needs to read much less data (thanks to CLUSTER) so the cache =
 =

stays empty.