Re: [PERFORM] Odd behavior with indices

2016-03-04 Thread Tom Lane
Merlin Moncure writes: > On Mon, Feb 29, 2016 at 12:47 PM, Tom Lane wrote: >> FWIW, PG >= 9.5 will ignore a LIMIT 1 inside an EXISTS, so that you get >> the same plan with or without it. But that does act as an optimization >> fence in earlier releases.

Re: [PERFORM] Odd behavior with indices

2016-03-04 Thread Merlin Moncure
On Mon, Feb 29, 2016 at 12:47 PM, Tom Lane wrote: > FWIW, PG >= 9.5 will ignore a LIMIT 1 inside an EXISTS, so that you get > the same plan with or without it. But that does act as an optimization > fence in earlier releases. Does 'offset 0' still work as it did? merlin

Re: [PERFORM] Odd behavior with indices

2016-02-29 Thread Tom Lane
Matheus de Oliveira writes: > Em 26 de fev de 2016 4:44 PM, "joe meiring" > escreveu: >> The same query for parameters is rather slow and does NOT use the index: >> >> EXPLAIN ANALYZE >> select * >> from parameter >> where exists ( >> select 1

Re: [PERFORM] Odd behavior with indices

2016-02-28 Thread Matheus de Oliveira
Em 26 de fev de 2016 4:44 PM, "joe meiring" escreveu: > > The same query for parameters is rather slow and does NOT use the index: > > EXPLAIN ANALYZE > select * > from parameter > where exists ( > select 1 from datavalue > where datavalue.parameter_id =

Re: [PERFORM] Odd behavior with indices

2016-02-26 Thread David G. Johnston
On Fri, Feb 26, 2016 at 1:38 PM, joe meiring wrote: > Here's the distribution of parameter_id's > > select count(parameter_id), parameter_id from datavalue group by parameter_id > 88169 142889171 815805 178570124257262 213947049 151225902 24091090 > 3103877

Re: [PERFORM] Odd behavior with indices

2016-02-26 Thread joe meiring
Here's the distribution of parameter_id's select count(parameter_id), parameter_id from datavalue group by parameter_id 88169 142889171 815805 178570124257262 213947049 151225902 24091090 3103877 10633764 11994442 1849232 2014935 4563638 132955919 7 On Fri, Feb 26, 2016 at 2:02

Re: [PERFORM] Odd behavior with indices

2016-02-26 Thread David G. Johnston
On Fri, Feb 26, 2016 at 12:43 PM, joe meiring wrote: > Also available on S.O.: > > > http://stackoverflow.com/questions/35658238/postgres-odd-behavior-with-indices > > I've got a datavalue table with ~200M rows or so, with indices on both > site_id and parameter_id. I