On 15/08/07, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On 8/15/07, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote: > > On 15/08/07, Gregory Stark <stark@xxxxxxxxxxxxxxxx> wrote: > > > "Phoenix Kiula" <phoenix.kiula@xxxxxxxxx> writes: > > > > > > > I'm grappling with a lot of reporting code for our app that relies on > > > > queries such as: > > > > > > > > SELECT COUNT(*) FROM TABLE WHERE ....(conditions)... > > > >... > > > > The number of such possibilities for multiple WHERE conditions is > > > > infinite... > > > > > > Depends on the "conditions" bit. You can't solve all of the infinite > > > possibilities -- well you can, just run the query above -- but if you want > to do better it's all about understanding your data. > > > > > > I am not sure what the advice here is. The WHERE condition comes from > > the indices. So if the query was not "COUNT(*)" but just a couple of > > columns, the query executes in less than a second. Just that COUNT(*) > > becomes horribly slow. > > Sorry, but I don't believe you. if you're doing a count(*) on the > same dataset that returns in < 1 second, then the count(*) with the > same where clause will run in < 1 second. I haven't seen pgsql do > anything else. Sorry I was not clear. Imagine an Amazon.com search results page. It has about 15 results on Page 1, then it shows "Page 1 of 190". To show each page, the query probably has a "LIMIT 15 OFFSET 0" for Page 1. However, to calculate the total number of pages, they probably do a separate counts query, because doing a "select *" and then counting the number of rows returned would be even more inefficient than a count(*). So, in reporting, two queries are fairly common I would think, unless I am missing something? ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
