In response to "Phoenix Kiula" <phoenix.kiula@xxxxxxxxx>: > > Yes, optimization. :) You don't need an exact count to tell someone > > that there's more data and they can go to it. > > > In general, I agree. But my example of Amazon was only to illustrate > the point about two queries and why they may be needed. I seem to see > many more pages than you do, but in any case, Google and Amazon can > afford to be less precise. > > Thanks for the suggestion of using EXPLAIN and parsing an > approximation, but when you need to show a trader how many trades he > has made, for instance, then approximation is not a possibility at > all. Especially not if the numbers sway so wildly -- > > > FIRSTDB=# explain select * from trades where t_id = 'kXjha'; > QUERY PLAN > ----------------------------------------------------------------------------------- > Bitmap Heap Scan on trades (cost=15.77..1447.12 rows=374 width=224) > Recheck Cond: ((t_id)::text = 'kXjha'::text) > -> Bitmap Index Scan on trades_tid_date (cost=0.00..15.67 rows=374 width=0) > Index Cond: ((t_id)::text = 'kXjha'::text) > (4 rows) > > FIRSTDB=# select count(*) from trades where t_id = 'kXjha'; > count > ------- > 3891 > (1 row) > > > > Could I do something so that the EXPLAIN showed up with slightly more > close-to-accurate stats? The above query is just after a "vacuum > analyze"! In the above case, you could probably materialize the data with a trigger that updates a counter in a separate table every time a new trade is added. This will give you 100% accurate results with _very_ fast response time. Part of the problem is that there's no one answer to your question, there are multiple approaches to solving it, depending on the details of the problem and the acceptable time/accuracy of the answers. Some basic approaches: 1) Materialize the data. MySQL actually does this automatically for you with MyISAM tables, which is why count(*) is so fast. But if you absolutely need fast, accurate counts, you can build your own triggers in PG. This is unlikely to be practical with all queries. 2) Estimate. The accuracy of estimates can vary wildly by query and how often the database is analyzed, etc. For something like, "show results 1 - 10 of about 50,000", estimates are great and fast, but for other cases, not acceptable. The good news is you can get a fast estimate from any query with no up-front work. 3) Accept that sometimes to get accurate answers it's going to take time. Around here, we call it the "Orbitz" technique, because when we discuss it, everyone thinks of the "please wait while I process your query" page you get from orbitz.com. You'd be surprised how willing your users are to wait, as long as they know they have to wait. 4) Throw more hardware at it. If you absolutely _must_have_ super- accurate results faster, then you may need to buy more RAM, faster disks and faster CPUs to accomplish it. 5) Come up with something revolutionary that nobody's every thought of before. Good luck with this one. Of course, all of these ideas are only practical if you've already ensured that your system is properly tuned. Crappy values for shared_buffers and other tuning will lead you to waste time trying to redesign something that should work just fine, so verify all your configuration first. You may be able to get more acceptable estimates by increasing your statistics targets, for example. -- Bill Moran http://www.potentialtech.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
