Re: Discovering the most searched values for a field

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...