On 31 Mar 2016 03:02, "bricklen" wrote:
> Perhaps a partial index like "create index pa_sc_id_pidx on pa (sc_id) with
> (fillfactor=100) where field1 IS NULL;" will help?
Thanks for the suggestion.
It might, but the problem with that is there's (something like)
field2-16 which are used in simil
On Tue, Mar 29, 2016 at 3:47 AM, Geoff Winkless wrote:
> On 28 March 2016 at 20:23, I wrote:
>
>> Table pa has 7522676 rows, 4834042 of which have field1 NULL, so it's
>> absolutely not reasonable to expect this to be an optimal strategy.
>>
>>
> It occurred to me that even though the majority
On Tue, Mar 29, 2016 at 6:47 AM, Geoff Winkless wrote:
> On 28 March 2016 at 20:23, I wrote:
>
>> Table pa has 7522676 rows, 4834042 of which have field1 NULL, so it's
>> absolutely not reasonable to expect this to be an optimal strategy.
>>
>>
> It occurred to me that even though the majority
On 28 March 2016 at 20:23, I wrote:
> Table pa has 7522676 rows, 4834042 of which have field1 NULL, so it's
> absolutely not reasonable to expect this to be an optimal strategy.
>
>
It occurred to me that even though the majority of values are NULL, there
are
1691 unique values in pa.field1,
On 28 March 2016 at 22:01, rob stone wrote:
> What does:-
>
> DELETE FROM pa
> WHERE pa.field1 IS NULL
> AND pa.sc_id IN (SELECT legs.sc_id FROM legs
> WHERE legs.scdate BETWEEN 20160220 AND > 20160222)
>
> give as a cost when you run ANALYZE over it?
>
Thanks for the suggestion.
It's a
pprox
On Mon, 2016-03-28 at 20:23 +0100, Geoff Winkless wrote:
> So I accept that when using MIN(sc_id) against scdate it makes
> statistical sense to use the sc_id index for a reasonable percentage
> of the full range of scdate, unless we know in advance that scdate is
> closely correlated to sc_id (bec
So I accept that when using MIN(sc_id) against scdate it makes statistical
sense to use the sc_id index for a reasonable percentage of the full range
of scdate, unless we know in advance that scdate is closely correlated to
sc_id (because using MIN means we can stop immediately we hit a value).
Ho