2009/8/18 Slava Moudry <smou...@4info.net>: > Hi Scott, > Thank you for reply. > I am using Postgres 8.4.0 (btw - great release --very happy about it) and I > got a different plan after following your advice:
Yeah, you're returning most of the rows, so a seq scan makes sense. Try indexing / matching on something more uncommon and you should get an index scan. > The seq scan is OK, since I don't expect Postgres to use index scan for such > low-selective condition. > It would be tough for me to support indexes for each bit flag value and their > combinations. E.g. in the query below it is again 200x off on number of rows. increase default stats target, analyze, try again. > explain analyze select count(*) from staging.tmp_t where mt_flags&134=0; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=83054.43..83054.44 rows=1 width=0) (actual > time=2964.960..2964.960 rows=1 loops=1) > -> Seq Scan on tmp_t (cost=0.00..83023.93 rows=12200 width=0) (actual > time=0.014..2152.031 rows=2362257 loops=1) > Filter: ((mt_flags & 134) = 0) > Total runtime: 2965.009 ms > (4 rows) > > I still wonder if it's something I could/should report as a bug? I've been > struggling with this issue in 8.2, 8.3.x (now using 8.4.0). > We can more or less work around this by disabling nestloop in our analytics > queries but I have problems enforcing this in reporting applications. Looks more like a low stats target. Try increasing that first. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
