The plan is unaltered . There is a separate index on theDate as well as one on node_id I have not specifically disabled sequential scans. This query performs much better on 8.1.9 on a similar sized table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 ) On Wed, Jun 9, 2010 at 7:55 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Robert Haas <robertmhaas@xxxxxxxxx> writes: >> On Thu, Jun 3, 2010 at 4:37 PM, Anj Adu <fotographs@xxxxxxxxx> wrote: >>> Link to plan >>> >>> http://explain.depesz.com/s/kHa > >> Your problem is likely related to the line that's showing up in red: > >> Index Scan using dev4_act_dy_fact_2010_05_t3_thedate on >> dev4_act_dy_fact_2010_05_t3 a (cost=0.00..94041.89 rows=204276 >> width=60) (actual time=164533.725..164533.725 rows=0 loops=1) >> * Index Cond: ((thedate >= '2010-05-22 00:00:00'::timestamp >> without time area) AND (thedate <= '2010-05-22 00:00:00'::timestamp >> without time area)) >> * Filter: (node_id = $0) > > "timestamp without time area"? Somehow I think this isn't the true > unaltered output of EXPLAIN. > > I'm just guessing, since we haven't been shown any table schemas, > but what it looks like to me is that the planner is using an entirely > inappropriate index in which the "thedate" column is a low-order column. > So what looks like a nice tight indexscan range is actually a full-table > indexscan. The planner knows that this is ridiculously expensive, as > indicated by the high cost estimate. It would be cheaper to do a > seqscan, which leads me to think the real problem here is the OP has > disabled seqscans. > > It might be worth providing an index in which "thedate" is the only, or > at least the first, column. For this particular query, an index on > node_id and thedate would actually be ideal, but that might be too > specialized. > > regards, tom lane > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
