Re: PostgreSQL does not choose my indexes well

2020-04-25 Thread Arcadio Ortega Reinoso
I'm also wondering exactly which Postgres version this is. Also a good question. Thanks, Stephen postgresql-12/bionic-pgdg,now 12.2-2.pgdg18.04+1 amd64 [instalado] postgresql-client-12/bionic-pgdg,now 12.2-2.pgdg18.04+1 amd64 [instalado, automático] postgresql-client-common/bionic-pgdg,bioni

Re: PostgreSQL does not choose my indexes well

2020-04-25 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > Turns out to be because what was provided wasn't actually what was being > > used- there's a domain in there and that seems to gum up the works and > > make it so we don't consider the partial index as being something w

Re: PostgreSQL does not choose my indexes well

2020-04-24 Thread Jeff Janes
On Fri, Apr 24, 2020 at 2:33 PM Stephen Frost wrote: > Greetings, > > * Jeff Janes (jeff.ja...@gmail.com) wrote: > > In order to read 1409985 / 12839 = 109 rows per buffer page, the table > must > > be extraordinarily well clustered on this index. That degree of > clustering > > is going to stea

Re: PostgreSQL does not choose my indexes well

2020-04-24 Thread Tom Lane
Stephen Frost writes: > Turns out to be because what was provided wasn't actually what was being > used- there's a domain in there and that seems to gum up the works and > make it so we don't consider the partial index as being something we can > use (see the discussion at the end of the other sub

Re: PostgreSQL does not choose my indexes well

2020-04-24 Thread Stephen Frost
Greetings, * Jeff Janes (jeff.ja...@gmail.com) wrote: > In order to read 1409985 / 12839 = 109 rows per buffer page, the table must > be extraordinarily well clustered on this index. That degree of clustering > is going to steal much of the thunder from the index-only scan. But in my > hands, it

Re: PostgreSQL does not choose my indexes well

2020-04-24 Thread Jeff Janes
On Thu, Apr 23, 2020 at 7:36 AM Arcadio Ortega Reinoso < arcadio.ort...@gmail.com> wrote: > explain (analyze, buffers, format text) select * from entidad where > cod_tabla = 4 > > > Index Scan using idx_tabla_entidad on entidad (cost=0.56..51121.41 > rows=1405216 width=20) (actual time=0.037..242.

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Arcadio Ortega Reinoso
El 23/4/20 a las 22:45, Tom Lane escribió: "David G. Johnston" writes: I noticed that too...I suspect its related to the ANALYZE result: Index Scan using idx_tabla_entidad on entidad (cost=0.56..51121.41 rows=1405216 width=20) (actual time=0.037..242.609 rows=1409985 loops=1) Index Cond: ((

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Tom Lane
"David G. Johnston" writes: > I noticed that too...I suspect its related to the ANALYZE result: > Index Scan using idx_tabla_entidad on entidad (cost=0.56..51121.41 > rows=1405216 width=20) (actual time=0.037..242.609 rows=1409985 loops=1) >Index Cond: ((cod_tabla)::bigint = 4) Yeah, that *s

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread David G. Johnston
On Thu, Apr 23, 2020 at 1:33 PM Tom Lane wrote: > I find the apparently > unnecessary cast in the partial-index predicate to be suspicious --- > maybe that's blocking matching to the WHERE clause? > I noticed that too...I suspect its related to the ANALYZE result: Index Scan using idx_tabla_ent

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Tom Lane
Stephen Frost writes: > I'm pretty suspicious that they've made some odd planner configuration > changes or something along those lines to end up with the plan they got, > or there's some reason we don't think we can use the partial index. Yeah, the latter is definitely a possibility. I find the

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > I do wonder if we are maybe missing a bet at times though, considering > > that I'm pretty sure we'll always go through the index in order, and > > therefore randomly, even when we don't actually need the results in > >

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Tom Lane
Stephen Frost writes: > I do wonder if we are maybe missing a bet at times though, considering > that I'm pretty sure we'll always go through the index in order, and > therefore randomly, even when we don't actually need the results in > order..? Has there been much consideration for just opening

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Stephen Frost
Greetings, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Thu, Apr 23, 2020 at 8:29 AM Tom Lane wrote: > > "David G. Johnston" writes: > > > On Thursday, April 23, 2020, Thomas Kellerer wrote: > > >> Plus: scanning idx_tabla_entidad is more efficient because that index is > > >> s

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Stephen Frost
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > "David G. Johnston" writes: > > On Thursday, April 23, 2020, Thomas Kellerer wrote: > >> Plus: scanning idx_tabla_entidad is more efficient because that index is > >> smaller. > > > Really? The absence of 33 million rows in the partial index

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread David G. Johnston
On Thu, Apr 23, 2020 at 8:29 AM Tom Lane wrote: > "David G. Johnston" writes: > > On Thursday, April 23, 2020, Thomas Kellerer wrote: > >> Plus: scanning idx_tabla_entidad is more efficient because that index is > >> smaller. > > > Really? The absence of 33 million rows in the partial index se

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Tom Lane
Michael Lewis writes: >> "unless the table is mostly marked all-visible" > Is that taken into account during planning when evaluating index scan vs > sequential scan? It is, although the planner's estimate is based on what the last ANALYZE saw, which might be out-of-date.

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Michael Lewis
> > "unless the table is mostly marked all-visible" Is that taken into account during planning when evaluating index scan vs sequential scan?

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Tom Lane
"David G. Johnston" writes: > On Thursday, April 23, 2020, Thomas Kellerer wrote: >> Plus: scanning idx_tabla_entidad is more efficient because that index is >> smaller. > Really? The absence of 33 million rows in the partial index seems like it > would compensate fully and then some for the ex

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread David G. Johnston
On Thursday, April 23, 2020, Thomas Kellerer wrote: > > CREATE INDEX idx_tabla_entidad > > ON public.entidad USING btree > > (cod_tabla ASC NULLS LAST); > > > > CREATE INDEX idx_entidad_tabla_4 > > ON public.entidad USING btree > > (cod_entidad_tabla ASC NULLS LAST) > > INCLUD

Re: PostgreSQL does not choose my indexes well

2020-04-23 Thread Thomas Kellerer
> CREATE INDEX idx_tabla_entidad >     ON public.entidad USING btree >     (cod_tabla ASC NULLS LAST); > > CREATE INDEX idx_entidad_tabla_4 >     ON public.entidad USING btree >     (cod_entidad_tabla ASC NULLS LAST) >     INCLUDE(cod_entidad, cod_tabla, cod_entidad_tabla) >     WHERE cod_tabla::bi

PostgreSQL does not choose my indexes well

2020-04-23 Thread Arcadio Ortega Reinoso
Hi, I don't understand why postgresql doesn't use clearly the most optimal index for a query in PLAN. Can you help me? create table public.tabla (     cod_tabla bigint not null,     tabla varchar(31) not null,     constraint pk_tabla primary key (cod_tabla) ); create table public.entidad (