Message-ID: <4F1D9857.90707@gmail.com>
Date: Mon, 23 Jan 2012 15:26:47 -0200
From: alexandre - aldeia digital <adaldeia@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:8.0) Gecko/20111124 Thunderbird/8.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Discovering the most searched values for a field
References: <4F10733B.3000704@gmail.com> <4F108122.3040400@agliodbs.com>
In-Reply-To: <4F108122.3040400@agliodbs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Em 13-01-2012 17:08, Josh Berkus escreveu:
> On 1/13/12 10:08 AM, alexandre - aldeia digital wrote:
>> Hi,
>>
>> Is there a simple way (or a tool) to discover the most searched values
>> in a field from a table ?
>>
>> In the pg_stats, I can see the most common values generated by ANALYZE,
>> but I want to know how many queries are using this values. With this
>> information and the other statistics, I want to create partial indexes
>> or use table partitioning to create some benchmarks to speed up the
>> database access.
>
> No simple + fast way.
>
> The way to do this is:
>
> 1) log all queries
> 2) load query log into a database
> 3) filter to queries which only run against that table
> 4) analyze queries for values against that column.
>
> For (4), we've had the best luck with generating explain plans in XML
> and then digesting the XML to look for filter conditions.  Finding
> column matches by regex was a lot less successful.
>

Thanks Josh ! I will try this. The only problem is the size of the LOGs. 
One day with logs turned on generates 100 GB log file in the most of my 
customers...