From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Robert Wille" <a2om6sy02(at)sneakemail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query planner plans very inefficient plans |
Date: | 2003-06-30 19:05:26 |
Message-ID: | 16644.1056999926@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"Robert Wille" <a2om6sy02(at)sneakemail(dot)com> writes:
> select * from image natural join ancestry where ancestorid=1000000 and
> (state & 7::bigint) = 0::bigint;
The planner is not going to have any statistics that allow it to predict
the number of rows satisfying that &-condition, and so it's unsurprising
if its off-the-cuff guess has little to do with reality.
I'd recommend skipping any cute tricks with bit-packing, and storing the
state (and any other values you query frequently) as its own column, so
that the query looks like
select * from image natural join ancestry where ancestorid=1000000 and
state = 0;
ANALYZE should be able to do a reasonable job with a column that has 8
or fewer distinct values ...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Chittenden | 2003-06-30 21:13:36 | Re: Query planner plans very inefficient plans |
Previous Message | Robert Wille | 2003-06-30 17:57:20 | Query planner plans very inefficient plans |