Re: Order by (for 15 rows) adds 30 seconds to query time

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(at)cam(dot)ac(dot)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