Thread: Slow join on partitioned table

The query plan appends sequential scans on the tables in the partition
(9 tables, ~4 million rows) and then hash joins that with a 14 row
table. The join condition is the primary key of each table in the
partition (and would be the primary key of the parent if that was
supported).
It would be much faster if it did an index scan on each of the child
tables and merged the results.

I can achieve this manually by rewriting the query as a union between
queries against each of the child tables. Is there a better way? (I'm
using PostGreSQL 8.4 with PostGIS 1.4).

Regards,
Mark Thornton

The query:

select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
         from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
         join "MasterRoadLinks" on
"RoadLinkInformation".roadLinkID="MasterRoadLinks".featureID

Table definitions

create temporary table LinkIds (featureid bigint not null)

create table "RoadLinkInformation" (
     rriid bigint not null primary key,
     roadlinkid bigint not null,
     point geometry,
     bound geometry
)

create table "MasterRoadLinks" (
             featureId bigint not null,
             centreLine geometry not null,
... other columns omitted for clarity
             )

All "RoadLinks/*" tables are children with the same structure and
featureId as the primary key.

The LinkIds table is the result of a previous query and contains just 14
rows (in this example).

Running the query against a view constructed as a union of the child
tables results in essentially the same query plan.

                                                                                       QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=16097.75..191987.73 rows=33266 width=683) (actual
time=3003.302..5387.541 rows=14 loops=1)
    Hash Cond: (public."MasterRoadLinks".featureid =
"RoadLinkInformation".roadlinkid)
    ->  Append  (cost=0.00..159902.54 rows=4130254 width=583) (actual
time=2.357..4056.404 rows=4129424 loops=1)
          ->  Seq Scan on "MasterRoadLinks"  (cost=0.00..18.30 rows=830
width=40) (actual time=0.002..0.002 rows=0 loops=1)
          ->  Seq Scan on "RoadLinks/A Road" "MasterRoadLinks"
(cost=0.00..22531.32 rows=378732 width=519) (actual time=2.352..268.170
rows=378732 loops=1)
          ->  Seq Scan on "RoadLinks/B Road" "MasterRoadLinks"
(cost=0.00..6684.19 rows=182819 width=587) (actual time=0.008..114.671
rows=182819 loops=1)
          ->  Seq Scan on "RoadLinks/Alley" "MasterRoadLinks"
(cost=0.00..2973.31 rows=100731 width=353) (actual time=0.008..59.283
rows=100731 loops=1)
          ->  Seq Scan on "RoadLinks/Local Street" "MasterRoadLinks"
(cost=0.00..67255.79 rows=2063279 width=450) (actual
time=0.048..1281.454 rows=2063279 loops=1)
          ->  Seq Scan on "RoadLinks/Minor Road" "MasterRoadLinks"
(cost=0.00..30733.42 rows=722942 width=784) (actual time=0.047..517.770
rows=722942 loops=1)
          ->  Seq Scan on "RoadLinks/Motorway" "MasterRoadLinks"
(cost=0.00..683.03 rows=15403 width=820) (actual time=0.005..10.809
rows=15403 loops=1)
          ->  Seq Scan on "RoadLinks/Pedestrianised Street"
"MasterRoadLinks"  (cost=0.00..92.93 rows=2993 width=399) (actual
time=0.008..1.971 rows=2993 loops=1)
          ->  Seq Scan on "RoadLinks/Private Road - Publicly Accessible"
"MasterRoadLinks"  (cost=0.00..1187.79 rows=30579 width=662) (actual
time=0.006..21.177 rows=30579 loops=1)
          ->  Seq Scan on "RoadLinks/Private Road - Restricted Access"
"MasterRoadLinks"  (cost=0.00..27742.46 rows=631946 width=855) (actual
time=0.047..459.302 rows=631946 loops=1)
    ->  Hash  (cost=16071.00..16071.00 rows=2140 width=116) (actual
time=0.205..0.205 rows=14 loops=1)
          ->  Nested Loop  (cost=0.00..16071.00 rows=2140 width=116)
(actual time=0.045..0.183 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.006..0.012 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..7.48 rows=1 width=116) (actual
time=0.008..0.009 rows=1 loops=14)
                      Index Cond: ("RoadLinkInformation".rriid =
linkids.featureid)
  Total runtime: 5387.734 ms
(19 rows)

On Fri, Mar 4, 2011 at 6:40 AM, Mark Thornton <mthornton@optrak.co.uk> wrote:
> The query plan appends sequential scans on the tables in the partition (9
> tables, ~4 million rows) and then hash joins that with a 14 row table. The
> join condition is the primary key of each table in the partition (and would
> be the primary key of the parent if that was supported).
> It would be much faster if it did an index scan on each of the child tables
> and merged the results.
>
> I can achieve this manually by rewriting the query as a union between
> queries against each of the child tables. Is there a better way? (I'm using
> PostGreSQL 8.4 with PostGIS 1.4).

Can you post the EXPLAIN ANALYZE output of the other formulation of the query?

>               ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140 width=8)
> (actual time=0.006..0.012 rows=14 loops=1)

That seems quite surprising.  There are only 14 rows in the table but
PG thinks 2140?  Do you have autovacuum turned on?  Has this table
been analyzed recently?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 04/03/2011 16:07, Robert Haas wrote:
> On Fri, Mar 4, 2011 at 6:40 AM, Mark Thornton<mthornton@optrak.co.uk>  wrote:
>> I can achieve this manually by rewriting the query as a union between
>> queries against each of the child tables. Is there a better way? (I'm using
>> PostGreSQL 8.4 with PostGIS 1.4).
> Can you post the EXPLAIN ANALYZE output of the other formulation of the query?

See below (at bottom)
>
> That seems quite surprising.  There are only 14 rows in the table but
> PG thinks 2140?  Do you have autovacuum turned on?  Has this table
> been analyzed recently?
>
It is a temporary table and thus I hadn't thought to analyze it. How
should such tables be treated? Should I analyze it immediately after
creation (i.e. when it is empty), after filling it or ... ? The expected
usage is such that the temporary table will have less than 100 or so rows.

However I now find that if I do analyze it I get the better result (plan
immediatley below). Curiously this result only applies to the inherited
(child table) formulation and not to the apparently equivalent query
over a view of unions. The view of unions is the approach used with SQL
Server 2008 .

Thanks for your help,
Mark Thornton

                                                                                                        QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.00..1442.57 rows=218 width=683) (actual
time=0.193..1.287 rows=14 loops=1)
    Join Filter: ("RoadLinkInformation".roadlinkid = links.featureid)
    ->  Nested Loop  (cost=0.00..118.19 rows=14 width=116) (actual
time=0.044..0.200 rows=14 loops=1)
          ->  Seq Scan on linkids  (cost=0.00..1.14 rows=14 width=8)
(actual time=0.011..0.020 rows=14 loops=1)
          ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..8.35 rows=1 width=116) (actual
time=0.009..0.010 rows=1 loops=14)
                Index Cond: ("RoadLinkInformation".rriid =
linkids.featureid)
    ->  Append  (cost=0.00..84.03 rows=839 width=46) (actual
time=0.051..0.061 rows=1 loops=14)
          ->  Seq Scan on "MasterRoadLinks" links  (cost=0.00..18.30
rows=830 width=40) (actual time=0.000..0.000 rows=0 loops=14)
          ->  Index Scan using "RoadLinks/A Road_pkey" on "RoadLinks/A
Road" links  (cost=0.00..7.36 rows=1 width=519) (actual
time=0.007..0.007 rows=0 loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
          ->  Index Scan using "RoadLinks/B Road_pkey" on "RoadLinks/B
Road" links  (cost=0.00..7.26 rows=1 width=587) (actual
time=0.006..0.006 rows=0 loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
          ->  Index Scan using "RoadLinks/Alley_pkey" on
"RoadLinks/Alley" links  (cost=0.00..7.24 rows=1 width=353) (actual
time=0.005..0.005 rows=0 loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
          ->  Index Scan using "RoadLinks/Local Street_pkey" on
"RoadLinks/Local Street" links  (cost=0.00..7.67 rows=1 width=450)
(actual time=0.008..0.008 rows=0 loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
          ->  Index Scan using "RoadLinks/Minor Road_pkey" on
"RoadLinks/Minor Road" links  (cost=0.00..7.37 rows=1 width=784) (actual
time=0.007..0.007 rows=0 loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
          ->  Index Scan using "RoadLinks/Motorway_pkey" on
"RoadLinks/Motorway" links  (cost=0.00..7.18 rows=1 width=820) (actual
time=0.005..0.005 rows=0 loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
          ->  Index Scan using "RoadLinks/Pedestrianised Street_pkey" on
"RoadLinks/Pedestrianised Street" links  (cost=0.00..7.08 rows=1
width=399) (actual time=0.004..0.004 rows=0 loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
          ->  Index Scan using "RoadLinks/Private Road - Publicly
Accessible_pkey" on "RoadLinks/Private Road - Publicly Accessible"
links  (cost=0.00..7.23 rows=1 width=662) (actual time=0.005..0.005
rows=0 loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
          ->  Index Scan using "RoadLinks/Private Road - Restricted
Access_pkey" on "RoadLinks/Private Road - Restricted Access" links
(cost=0.00..7.35 rows=1 width=855) (actual time=0.008..0.009 rows=1
loops=14)
                Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
  Total runtime: 1.518 ms
(27 rows)


Query plan with alternative query:

                                                                                                        QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Append  (cost=0.00..168893.14 rows=11237 width=723) (actual
time=0.934..234.609 rows=14 loops=1)
    ->  Nested Loop  (cost=0.00..22222.78 rows=2140 width=619) (actual
time=0.291..0.291 rows=0 loops=1)
          ->  Nested Loop  (cost=0.00..13811.00 rows=2140 width=116)
(actual time=0.049..0.181 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.013..0.018 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..6.43 rows=1 width=116) (actual
time=0.008..0.009 rows=1 loops=14)
                      Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
          ->  Index Scan using "RoadLinks/A Road_pkey" on "RoadLinks/A
Road" links  (cost=0.00..3.91 rows=1 width=519) (actual
time=0.007..0.007 rows=0 loops=14)
                Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
    ->  Nested Loop  (cost=0.00..14729.95 rows=1472 width=687) (actual
time=0.231..0.231 rows=0 loops=1)
          ->  Nested Loop  (cost=0.00..13895.00 rows=2140 width=116)
(actual time=0.013..0.134 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.011 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..6.47 rows=1 width=116) (actual
time=0.006..0.007 rows=1 loops=14)
                      Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
          ->  Index Scan using "RoadLinks/B Road_pkey" on "RoadLinks/B
Road" links  (cost=0.00..0.37 rows=1 width=587) (actual
time=0.006..0.006 rows=0 loops=14)
                Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
    ->  Nested Loop  (cost=0.00..14604.60 rows=811 width=453) (actual
time=0.215..0.215 rows=0 loops=1)
          ->  Nested Loop  (cost=0.00..13895.00 rows=2140 width=116)
(actual time=0.011..0.127 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.009 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..6.47 rows=1 width=116) (actual
time=0.006..0.007 rows=1 loops=14)
                      Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
          ->  Index Scan using "RoadLinks/Alley_pkey" on
"RoadLinks/Alley" links  (cost=0.00..0.32 rows=1 width=353) (actual
time=0.005..0.005 rows=0 loops=14)
                Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
    ->  Nested Loop  (cost=0.00..28899.43 rows=2140 width=550) (actual
time=0.194..0.323 rows=4 loops=1)
          ->  Nested Loop  (cost=0.00..14767.00 rows=2140 width=116)
(actual time=0.011..0.136 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.010 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..6.87 rows=1 width=116) (actual
time=0.006..0.007 rows=1 loops=14)
                      Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
          ->  Index Scan using "RoadLinks/Local Street_pkey" on
"RoadLinks/Local Street" links  (cost=0.00..6.59 rows=1 width=450)
(actual time=0.007..0.007 rows=0 loops=14)
                Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
    ->  Nested Loop  (cost=0.00..23803.64 rows=2140 width=884) (actual
time=0.228..0.228 rows=0 loops=1)
          ->  Nested Loop  (cost=0.00..13939.00 rows=2140 width=116)
(actual time=0.012..0.126 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.004..0.010 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..6.49 rows=1 width=116) (actual
time=0.006..0.006 rows=1 loops=14)
                      Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
          ->  Index Scan using "RoadLinks/Minor Road_pkey" on
"RoadLinks/Minor Road" links  (cost=0.00..4.59 rows=1 width=784) (actual
time=0.006..0.006 rows=0 loops=14)
                Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
    ->  Nested Loop  (cost=0.00..14517.88 rows=124 width=920) (actual
time=0.390..0.390 rows=0 loops=1)
          ->  Nested Loop  (cost=0.00..13895.00 rows=2140 width=116)
(actual time=0.011..0.142 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.012 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..6.47 rows=1 width=116) (actual
time=0.006..0.007 rows=1 loops=14)
                      Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
          ->  Index Scan using "RoadLinks/Motorway_pkey" on
"RoadLinks/Motorway" links  (cost=0.00..0.28 rows=1 width=820) (actual
time=0.005..0.005 rows=0 loops=14)
                Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
    ->  Hash Join  (cost=12418.00..12457.78 rows=24 width=499) (actual
time=232.495..232.495 rows=0 loops=1)
          Hash Cond: (pg_temp_1.linkids.featureid =
public."RoadLinkInformation".rriid)
          ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140 width=8)
(actual time=0.007..0.012 rows=14 loops=1)
          ->  Hash  (cost=12377.61..12377.61 rows=3231 width=499)
(actual time=232.421..232.421 rows=1125 loops=1)
                ->  Hash Join  (cost=130.34..12377.61 rows=3231
width=499) (actual time=11.572..230.975 rows=1125 loops=1)
                      Hash Cond:
(public."RoadLinkInformation".roadlinkid = links.featureid)
                      ->  Seq Scan on "RoadLinkInformation"
(cost=0.00..10422.28 rows=286828 width=116) (actual time=4.306..81.587
rows=286828 loops=1)
                      ->  Hash  (cost=92.93..92.93 rows=2993 width=399)
(actual time=7.029..7.029 rows=2993 loops=1)
                            ->  Seq Scan on "RoadLinks/Pedestrianised
Street" links  (cost=0.00..92.93 rows=2993 width=399) (actual
time=0.014..3.100 rows=2993 loops=1)
    ->  Nested Loop  (cost=0.00..14535.03 rows=246 width=762) (actual
time=0.168..0.168 rows=0 loops=1)
          ->  Nested Loop  (cost=0.00..13895.00 rows=2140 width=116)
(actual time=0.031..0.113 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.005..0.007 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..6.47 rows=1 width=116) (actual
time=0.005..0.006 rows=1 loops=14)
                      Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
          ->  Index Scan using "RoadLinks/Private Road - Publicly
Accessible_pkey" on "RoadLinks/Private Road - Publicly Accessible"
links  (cost=0.00..0.29 rows=1 width=662) (actual time=0.003..0.003
rows=0 loops=14)
                Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
    ->  Nested Loop  (cost=0.00..23009.69 rows=2140 width=955) (actual
time=0.058..0.256 rows=10 loops=1)
          ->  Nested Loop  (cost=0.00..13871.00 rows=2140 width=116)
(actual time=0.009..0.090 rows=14 loops=1)
                ->  Seq Scan on linkids  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.006 rows=14 loops=1)
                ->  Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation"  (cost=0.00..6.45 rows=1 width=116) (actual
time=0.004..0.004 rows=1 loops=14)
                      Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
          ->  Index Scan using "RoadLinks/Private Road - Restricted
Access_pkey" on "RoadLinks/Private Road - Restricted Access" links
(cost=0.00..4.25 rows=1 width=855) (actual time=0.005..0.005 rows=1
loops=14)
                Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
  Total runtime: 235.501 ms
(67 rows)

The alternative query

select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/A Road" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
         union all
         select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/B Road" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
         union all
         select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/Alley" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
         union all
         select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/Local Street" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
         union all
         select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/Minor Road" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
         union all
         select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/Motorway" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
         union all
         select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/Pedestrianised Street" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
         union all
         select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/Private Road - Publicly Accessible" as
Links on "RoadLinkInformation".roadLinkID=Links.featureID
         union all
         select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
                 from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
                 join "RoadLinks/Private Road - Restricted Access" as
Links on "RoadLinkInformation".roadLinkID=Links.featureID

On 04/03/2011 16:07, Robert Haas wrote:
> That seems quite surprising. There are only 14 rows in the table but
> PG thinks 2140?  Do you have autovacuum turned on?  Has this table
> been analyzed recently?
>
I think autovacuum is enabled, but as a temporary table LinkIds has only
existed for a very short time (at least in my current tests).

Mark

On Fri, Mar 4, 2011 at 12:00 PM, Mark Thornton <mthornton@optrak.co.uk> wrote:
> On 04/03/2011 16:07, Robert Haas wrote:
>>
>> That seems quite surprising. There are only 14 rows in the table but
>> PG thinks 2140?  Do you have autovacuum turned on?  Has this table
>> been analyzed recently?
>>
> I think autovacuum is enabled, but as a temporary table LinkIds has only
> existed for a very short time (at least in my current tests).

Autovacuum doesn't work on temporary tables, so time of existence
doesn't matter in that case.  The best approach is to load the data,
then analyze, then query it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Mar 4, 2011 at 8:47 AM, Mark Thornton <mthornton@optrak.co.uk> wrote:
> It is a temporary table and thus I hadn't thought to analyze it. How should
> such tables be treated? Should I analyze it immediately after creation (i.e.
> when it is empty), after filling it or ... ? The expected usage is such that
> the temporary table will have less than 100 or so rows.

When in doubt, analyze.

If you're engaging in OLAP, or some other workload pattern that
involves writing many small batches, then analyzing all the time is
bad.  If you're engaged in any workload that writes rarely - a bulk
insert here, a create-table-as-select there, etc - always analyze.

The cost of analyzing when you shouldn't is low and O(1) per analysis,
and the cost of not analyzing when you should have can easily be
O(n^2) or worse w/r/t data size.

The cost of analyzing is especially low on a temp table only owned by
your current session, because no one else will be disturbed by the
table lock you acquire if you context-switch out before it's done.

-Conor