On Wed, Jun 1, 2011 at 6:28 PM, CS DBA <cs_dba@xxxxxxxxxxxxxxxxxxx> wrote: > On 06/01/2011 03:38 PM, Kevin Grittner wrote: >> >> CS DBA<cs_dba@xxxxxxxxxxxxxxxxxxx> wrote: >> >>> The app wants to run a query like this: >>> >>> select count(pri_num) from max_xtrv_st_t >>> where pri_num in (select max(pri_num) from max_xtrv_st_t where 1=1 >>> group by tds_cx_ind, cxs_ind_2) >> >> Why not something simpler? There are a number of possibilities, and >> I don't claim this one is necessarily best (or even error free), but >> how about something like?: >> >> select count(*) from >> (select distinct max(pri_num) >> from max_xtrv_st_t >> group by tds_cx_ind, cxs_ind_2) x >> >> -Kevin > > I've tried a number of alternates, each one wants to do a seq scan of the > table (including your suggestion above). why wouldn't you expect a sequential scan? what is the number of unique values for tds_cx_ind, cxs_ind_2 on the table? one of the most important techniques with query optimization is to put yourself in the place of the database and try to imagine how *you* would pass over the records...then try and coerce the database into that plan. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
