am Wed, dem 26.11.2008, um 21:21:04 -0700 mailte Kevin Kempter folgendes: > Next we have a select count(*) that also one of the top offenders: > > select count(*) from public.tab3 where user_id=31 > and state='A' > and amount>0; > > QUERY PLAN > > ----------------------------------------------------------------------------------------------------- > Aggregate (cost=3836.53..3836.54 rows=1 width=0) > -> Index Scan using order_user_indx ontab3 user_id (cost=0.00..3834.29 > rows=897 width=0) > Index Cond: (idx_user_id = 31406948::numeric) > Filter: ((state = 'A'::bpchar) AND (amount > 0::numeric)) > (4 rows) > > We have an index on the user_id but not on the state or amount, > > add index to amount ?
Depends. - Is the index on user_id a unique index? - how many different values are in the table for state, i.e., maybe an index on state can help - how many rows in the table with amount > 0? If almost all rows contains an amount > 0 an index can't help in this case Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
