Index usage on a temporary table

Question

I have two rather simple queries. The first query

 UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;

and it's plan

 duration: 0.125 ms  plan:
    Query Text: UPDATE mp_physical  SET periodic_number = '' WHERE periodic_number is NULL;
    Update on mp_physical  (cost=0.42..7.34 rows=1 width=801)
      ->  Index Scan using "_I_periodic_number" on mp_physical  (cost=0.42..7.34 rows=1 width=801)
            Index Cond: (periodic_number IS NULL)

And the second one:

 UPDATE observations_optical_temp SET designation = '' WHERE periodic_number is NULL;

and it's plan:

duration: 2817.375 ms  plan:
    Query Text: UPDATE observations_optical_temp SET periodic_number = '' WHERE periodic_number is NULL;
    Update on observations_optical_temp  (cost=103.55..9223.01 rows=5049 width=212)
      ->  Bitmap Heap Scan on observations_optical_temp  (cost=103.55..9223.01 rows=5049 width=212)
            Recheck Cond: (periodic_number IS NULL)
            ->  Bitmap Index Scan on "_I_per_num_temp"  (cost=0.00..102.29 rows=5049 width=0)
                  Index Cond: (periodic_number IS NULL)

I expect the second plan shold be the same the first one. But it's not. Why? Here are dumps of the tables.

CREATE TABLE public.mp_physical(
    id_mpp serial NOT NULL,
    id_comet_parts integer,
    "SPK_id" public.nonnegative_int,
    designation varchar(30),
    name varchar(100),
    prefix varchar,
    "is_NEO" bool,
    "H" double precision,
    "G" double precision,
    diameter public.nonnegative_double,
    extent varchar(30),
    extent_error public.nonnegative_double,
    geometric_albedo public.nonnegative_double,
    rot_per public.nonnegative_double,
    "GM" public.nonnegative_double,
    "BV" public.nonnegative_double,
    "UB" public.nonnegative_double,
    "spec_B" varchar(30),
    "spec_T" varchar(30),
    lca double precision,
    multiplicity public.nonnegative_int,
    polar_ang double precision,
    polar_slope_ang double precision,
    a double precision,
    b double precision,
    mass public.nonnegative_double,
    mp_type public.mp_type NOT NULL,
    periodic_number varchar(5),
    diameter_method_def varchar(200),
    discovery_info text,
    "H_sigma" public.nonnegative_double,
    "G_sigma" public.nonnegative_double,
    diameter_sigma public.nonnegative_double,
    geometric_albedo_sigma public.nonnegative_double,
    rot_per_sigma public.nonnegative_double,
    "GM_sigma" public.nonnegative_double,
    "BV_sigma" public.nonnegative_double,
    "UB_sigma" public.nonnegative_double,
    lca_sigma public.nonnegative_double,
    a_sigma public.nonnegative_double,
    b_sigma public.nonnegative_double,
    polar_ang_sigma public.nonnegative_double,
    mass_sigma public.nonnegative_double,
    CONSTRAINT "_C_id_ap" PRIMARY KEY (id_mpp)
);

CREATE INDEX "_I_name" ON  mp_physical  USING btree (name);
CREATE INDEX "_I_designation" ON mp_physical USING btree(mpp_designation);
CREATE INDEX "_I_periodic_number" ON mp_physical USING btree(periodic_number);
CREATE INDEX "_I_mp_type" ON mp_physical USING btree(mp_type);

And

  CREATE TEMPORARY TABLE "observations_optical_temp"(note_1,date,"RA","Dec",magnitude,band,id_observatory,id_mpp,"Dec_degree",observatory_code,periodic_number,mpp_designation,mp_type)
AS SELECT note_1,date,"RA","Dec",magnitude,band,id_observatory,id_mpp,"Dec_degree",'1'::varchar(3),'1'::varchar(8),'1'::varchar(30),'A'::public.mp_type FROM observations_optical;

CREATE TABLE observations_optical(
    id_obs_o bigint
    note_1 varchar,
    date timestamp NOT NULL,
    "RA" time NOT NULL,
    "Dec_degree" integer NOT NULL,
    "Dec" time NOT NULL,
    magnitude double precision,
    band varchar,
    id_observatory integer,
    id_mpp integer,
    CONSTRAINT "_PK_id_obs_o" PRIMARY KEY (id_obs_o)
);
CREATE INDEX "_I_temp_1" ON observations_optical_temp USING btree(mpp_designation);
CREATE INDEX "_I_temp_2" ON observations_optical_temp USING btree(periodic_number);
CREATE INDEX "_I_temp_3" ON observations_optical_temp USING btree(mp_type);

Answer 1

The choice between index scan and bitmap index scan is basically decided by how many rows per data page Postgres expects to retrieve - which depends on statistics about data distribution in the table and selectivity of your query predicates. And by the expected cost of random memory access - which is defined by cost settings, most prominently random_page_cost and effective_cache_size.

If Postgres expects to find enpugh rows on the same data page, it switches to bitmap index scan, which is more efficient for this kind of physical data distribution. (If most data pages are retrieved, a lowly sequential scan is faster anyway.) So even if columns and indexes of your two tables look all the same, you may still get these different query plans depending on data distribution and selectivity of your query predicates.

• Optimizing queries on a range of timestamps (one column)
• "Recheck Cond:" line in query plans with a bitmap index scan

But your tables are very different to begin with. mp_physical has much wider rows, so that only few rows are located on the same data page (default 8kb). This heavily favors index scans, since bitmap index scans won't buy much (if anything) even when retrieving a higher percentage of all rows.

Also, be aware that temporary tables are not covered by autovacuum and hence not analyzed automatically. You may have to do this manually to get accurate table statistics for the query planner to work with:

ANALYZE observations_optical_temp;

• Are regular VACUUM ANALYZE still recommended under 9.1?