Re: Slow count(*) again...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tuesday 12 October 2010 14:35:01 you wrote:
> > suggest that 99% instances of the "select count(*)" idiom are probably
> > 
> >> bad use of the SQL language.
> 
> Well, suppose you paginate results. If the user sees that the search query
> returns 500 pages, there are two options :
> 
> - you're google, and your sorting algorithms are so good that the answer
> the user wants is in the first page
> - or the user will refine his search by entering more keywords tu get a
> manageable result set
> 
> So, in both cases, the count(*) was useless anyway. And the slowest ones
> are the most useless, since the user will immediatey discard the result
> and refine his query.
> 
> If your full text search is slow, try Xapian or Lucene.

I guess I have to comment here again and point out that while I am having this 
issue with text searches, I avoid using count(*) in such cases, I just use 
next and previous links. Where the real problem (for me) is that when someone 
searches a date or time range. My application keeps track of huge amounts of 
realtime transactional data. So an administrator might want a report as to 
what some data point did yesterday between 3 and 4 PM. Under normal conditions 
the range of records that match can be between 0 and over 5,000. This is 
really killing me especially when the reporting people want a list of how many 
transactions each that were on points in a given zipcode had this morning 
between 8 and 9 AM, it takes about 5 minutes to run on a server that has 
enough ram to hold the entire table!

Pseudo query:
Show how many transactions per node in zipcode 92252 between 8:00 and 9:00 
today:

point_number | number_of_transactions
65889	|	31
34814	|	4865
28349	|	0
3358	|	364
...

24 total rows, > 5 minutes.

Then they want every node to be a link to a list of actual data within the 
specified timeframe.
This is where I have to to the same query twice to first find out how many for 
the page links, then again to get a page of results.
Sure, I could keep tables around that have numbers by the hour, minute, day or 
whatever to cache up results for speeding things, then the problem is that 
when the data is put into the server, there are so many statistics tables to 
update, the front end becomes a huge problem. Also, it makes for a huge mess 
of tables to think about when I need to make a report.

-Neil-



-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux