[PERFORM] CREATE INDEX uses INDEX ?

[Nicolas Paris]

Hello,

I wonder if the process of index creation can benefit from other indexes.

EG: Creating a partial index with predicat based on a boolean column, will
use an hypothetic index on that boolean column or always use a seq scan on
all rows ?

Goal is to create partial indexes faster.

Explain command does not work with Create index.

Thanks by advance

Nicolas PARIS

Re: [PERFORM] CREATE INDEX uses INDEX ?

[Guillaume Lelarge]

Le 7 mars 2015 11:32, "Nicolas Paris"  a écrit :
>
> Hello,
>
> I wonder if the process of index creation can benefit from other indexes.
>

It cannot.

> EG: Creating a partial index with predicat based on a boolean column,
will use an hypothetic index on that boolean column or always use a seq
scan on all rows ?
>

Nope, it always does a seqscan.

> Goal is to create partial indexes faster.
>
> Explain command does not work with Create index.
>

You cannot use EXPLAIN on most DDL commands.

Re: [PERFORM] EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

[Tomas Vondra]

On 7.3.2015 03:26, Jeff Janes wrote:
> On Fri, Mar 6, 2015 at 5:38 PM, Tom Lane  > wrote:
> 
> But the actual query is using a seq scan, and so it would hint the
> table in efficient sequential order, rather than hinting the table
> haphazardly in index order like probing the endpoint does.

I think this has nothing to do with the plan itself, but with the
estimation in optimizer - that looks up the range from the index in some
cases, and that may generate random I/O to the table.

> 
>> Also, it's less than clear why only this particular query is
>> showing any stress. Dead rows should be a hazard for anything,
>> especially if there are enough of them to require hours to re-hint.
>> And why wouldn't autovacuum get to them first?
> 
> 
> Say the timing of this query is such that 10% of the parent turns
> over between invocations of this query, and that this 10% is all at
> the end of some index but random over the table heap. If autovac
> kicks in at 20% turn over, then half the time autovac does get to
> them first, and half the time it doesn't. It would be interesting to
> know if this query is bad every time it is planner, or just
> sometimes.

Yeah, this might be the reason. Another possibility is that this is part
of some large batch, and autovacuum simply did not have change to do the
work.

regards

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance