Re: Order by (for 15 rows) adds 30 seconds to query time - Mailing list pgsql-performance

Jean-Michel Pouré wrote:
> Le mardi 01 décembre 2009 à 18:52 +0000, Richard Neill a écrit :
>> Is this normal? Have I hit a bug?
>
> PostgreSQL query analyzer needs to run a couple of times before it can
> rewrite and optimize the query. Make sure demand_id, id and join IDs
> carry indexes.
>

I did, and they do. This table has been in place for ages, with
autovacuum on, and a manual vacuum analyze every night. I checked by
running analyze explicitly on all the relevant tables just before
posting this.

> Run EXPLAIN ANALYSE your_query to understand how the parser works and
> post it back here.
>

Already in previous email :-)

> Kind regards,
> Jean-Michel



Kevin Grittner wrote:
 > Richard Neill <rn214@cam.ac.uk> wrote:
 >
 >> I'd expect the ORDER BY to be the last thing that runs
 >
 >>   Nested Loop Left Join  (cost=0.00..727737158.77
 >> rows=806903677108 width=195) (actual time=31739.052..32862.322
 >> rows=15 loops=1)
 >
 > It probably would if it knew there were going to be 15 rows to sort.
 > It is estimating that there will be 806,903,677,108 rows, in which
 > case it thinks that using the index will be faster.  The question is
 > why it's 10 or 11 orders of magnitude off on the estimate of result
 > rows.  Could you show us the table definitions underlying that view?
 >
 > -Kevin
 >


Am I wrong in thinking that ORDER BY is always applied after the main
query is run?

Even if I run it this way:

select * from (select * from h.inventory where demand_id =289276563) as
sqry order by id;

which should(?) surely force it to run the first select, then sort, it's
still very slow. On the other hand, it's quick if I do order by id+1

The table definitions are as follows (sorry there are so many).


Richard







fswcs=# \d h.demand
            View "h.demand"
     Column     |  Type   | Modifiers
---------------+---------+-----------
  id            | bigint  |
  target_id     | bigint  |
  target_tag    | text    |
  target_name   | text    |
  material_id   | bigint  |
  material_tag  | text    |
  material_name | text    |
  qty           | integer |
  benefit       | integer |
View definition:
  SELECT demand.id, demand.target_id, h_target_waypoint.tag AS
target_tag, h_target_waypoint.name AS target_name, demand.material_id,
h_material.tag AS material_tag, h_material.name AS material_name,
demand.qty, demand.benefit
    FROM core.demand
    LEFT JOIN h.waypoint h_target_waypoint ON demand.target_id =
h_target_waypoint.id
    LEFT JOIN h.material h_material ON demand.material_id = h_material.id;

fswcs=# \d core.demand
                   Table "core.demand"
    Column    |  Type   |           Modifiers
-------------+---------+--------------------------------
  id          | bigint  | not null default core.new_id()
  target_id   | bigint  | not null
  material_id | bigint  | not null
  qty         | integer | not null
  benefit     | integer | not null default 0
Indexes:
     "demand_pkey" PRIMARY KEY, btree (id) CLUSTER
     "demand_target_id_key" UNIQUE, btree (target_id, material_id)
     "demand_material_id" btree (material_id)
     "demand_target_id" btree (target_id)
Foreign-key constraints:
     "demand_material_id_fkey" FOREIGN KEY (material_id) REFERENCES
core.__material_id(id)
     "demand_target_id_fkey" FOREIGN KEY (target_id) REFERENCES
core.waypoint(id)
Referenced by:
     TABLE "viwcs.du_report_contents" CONSTRAINT
"du_report_contents_demand_id_fkey" FOREIGN KEY (demand_id) REFERENCES
core.demand(id)
     TABLE "core.inventory" CONSTRAINT "inventory_demand_id_fkey"
FOREIGN KEY (demand_id) REFERENCES core.demand(id)
     TABLE "viwcs.wave_demand" CONSTRAINT "wave_demand_demand_id_fkey"
FOREIGN KEY (demand_id) REFERENCES core.demand(id)

fswcs=# \d h.waypoint
         View "h.waypoint"
   Column   |  Type   | Modifiers
-----------+---------+-----------
  id        | bigint  |
  tag       | text    |
  name      | text    |
  is_router | boolean |
  is_target | boolean |
  is_packer | boolean |
View definition:
  SELECT waypoint.id, waypoint.tag, waypoint.name, waypoint.is_router,
waypoint.is_target, waypoint.is_packer
    FROM core.waypoint;

fswcs=# \d h.material
       View "h.material"
  Column |  Type   | Modifiers
--------+---------+-----------
  id     | bigint  |
  tag    | text    |
  name   | text    |
  mass   | integer |
  volume | integer |
View definition:
  SELECT material.id, material.tag, material.name, material.mass,
material.volume
    FROM core.material;

fswcs=# \d core.wa
core.waypoint           core.waypoint_name_key  core.waypoint_pkey
core.waypoint_tag_key
fswcs=# \d core.waypoint
                 Table "core.waypoint"
   Column   |  Type   |           Modifiers
-----------+---------+--------------------------------
  id        | bigint  | not null default core.new_id()
  tag       | text    | not null
  name      | text    | not null
  is_router | boolean | not null
  is_target | boolean | not null
  is_packer | boolean | not null
Indexes:
     "waypoint_pkey" PRIMARY KEY, btree (id) CLUSTER
     "waypoint_tag_key" UNIQUE, btree (tag)
     "waypoint_name_key" btree (name)
Referenced by:
     TABLE "core.demand" CONSTRAINT "demand_target_id_fkey" FOREIGN KEY
(target_id) REFERENCES core.waypoint(id)
     TABLE "viwcs.du_report" CONSTRAINT "du_report_target_id_fkey"
FOREIGN KEY (target_id) REFERENCES core.waypoint(id)
     TABLE "viwcs.mss_actor_state" CONSTRAINT
"mss_actor_state_last_demand_tag_fkey" FOREIGN KEY (last_demand_tag)
REFERENCES core.waypoint(tag)
     TABLE "viwcs.mss_actor_state" CONSTRAINT
"mss_actor_state_last_racking_tag_fkey" FOREIGN KEY (last_racking_tag)
REFERENCES core.waypoint(tag)
     TABLE "viwcs.mss_rack_action_queue" CONSTRAINT
"mss_rack_action_queue_racking_tag_fkey" FOREIGN KEY (racking_tag)
REFERENCES core.waypoint(tag)
     TABLE "core.route_cache" CONSTRAINT "route_cache_next_hop_id_fkey"
FOREIGN KEY (next_hop_id) REFERENCES core.waypoint(id) ON DELETE CASCADE
     TABLE "core.route_cache" CONSTRAINT "route_cache_router_id_fkey"
FOREIGN KEY (router_id) REFERENCES core.waypoint(id) ON DELETE CASCADE
     TABLE "core.route_cache" CONSTRAINT "route_cache_target_id_fkey"
FOREIGN KEY (target_id) REFERENCES core.waypoint(id) ON DELETE CASCADE
     TABLE "core.route" CONSTRAINT "route_dst_id_fkey" FOREIGN KEY
(dst_id) REFERENCES core.waypoint(id)
     TABLE "core.route" CONSTRAINT "route_src_id_fkey" FOREIGN KEY
(src_id) REFERENCES core.waypoint(id)
     TABLE "viwcs.wave_genreorders_map" CONSTRAINT
"wave_genreorders_map_ERR_GENREID_UNKNOWN" FOREIGN KEY (target_id)
REFERENCES core.waypoint(id)
Triggers:
     __waypoint__location_id_delete BEFORE DELETE ON core.waypoint FOR
EACH ROW EXECUTE PROCEDURE core.__location_id_delete()
     __waypoint__location_id_insert BEFORE INSERT ON core.waypoint FOR
EACH ROW EXECUTE PROCEDURE core.__location_id_insert()
     __waypoint__location_id_update BEFORE UPDATE ON core.waypoint FOR
EACH ROW EXECUTE PROCEDURE core.__location_id_update()
     __waypoint__tag_id_delete BEFORE DELETE ON core.waypoint FOR EACH
ROW EXECUTE PROCEDURE core.__tag_id_delete()
     __waypoint__tag_id_insert BEFORE INSERT ON core.waypoint FOR EACH
ROW EXECUTE PROCEDURE core.__tag_id_insert()
     __waypoint__tag_id_update BEFORE UPDATE ON core.waypoint FOR EACH
ROW EXECUTE PROCEDURE core.__tag_id_update()
     __waypoint__tag_tag_delete BEFORE DELETE ON core.waypoint FOR EACH
ROW EXECUTE PROCEDURE core.__tag_tag_delete()
     __waypoint__tag_tag_insert BEFORE INSERT ON core.waypoint FOR EACH
ROW EXECUTE PROCEDURE core.__tag_tag_insert()
     __waypoint__tag_tag_update BEFORE UPDATE ON core.waypoint FOR EACH
ROW EXECUTE PROCEDURE core.__tag_tag_update()
Inherits: core.location

fswcs=# \d core.ma
core.material           core.material_name_key  core.material_pkey
core.material_tag_key
fswcs=# \d core.material
                Table "core.material"
  Column |  Type   |           Modifiers
--------+---------+--------------------------------
  id     | bigint  | not null default core.new_id()
  tag    | text    | not null
  name   | text    | not null
  mass   | integer | not null
  volume | integer | not null
Indexes:
     "material_pkey" PRIMARY KEY, btree (id)
     "material_tag_key" UNIQUE, btree (tag)
     "material_name_key" btree (name)
Check constraints:
     "material_mass_check" CHECK (mass >= 0)
     "material_volume_check" CHECK (volume >= 0)
Triggers:
     __material__material_id_delete BEFORE DELETE ON core.material FOR
EACH ROW EXECUTE PROCEDURE core.__material_id_delete()
     __material__material_id_insert BEFORE INSERT ON core.material FOR
EACH ROW EXECUTE PROCEDURE core.__material_id_insert()
     __material__material_id_update BEFORE UPDATE ON core.material FOR
EACH ROW EXECUTE PROCEDURE core.__material_id_update()
     __material__tag_id_delete BEFORE DELETE ON core.material FOR EACH
ROW EXECUTE PROCEDURE core.__tag_id_delete()
     __material__tag_id_insert BEFORE INSERT ON core.material FOR EACH
ROW EXECUTE PROCEDURE core.__tag_id_insert()
     __material__tag_id_update BEFORE UPDATE ON core.material FOR EACH
ROW EXECUTE PROCEDURE core.__tag_id_update()
     __material__tag_tag_delete BEFORE DELETE ON core.material FOR EACH
ROW EXECUTE PROCEDURE core.__tag_tag_delete()
     __material__tag_tag_insert BEFORE INSERT ON core.material FOR EACH
ROW EXECUTE PROCEDURE core.__tag_tag_insert()
     __material__tag_tag_update BEFORE UPDATE ON core.material FOR EACH
ROW EXECUTE PROCEDURE core.__tag_tag_update()
Inherits: core.tag