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
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
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
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
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
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.
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: ((
"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
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
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
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
> >
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
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
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
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
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.
>
> "unless the table is mostly marked all-visible"
Is that taken into account during planning when evaluating index scan vs
sequential scan?
"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
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
> 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
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
(
21 matches
Mail list logo