Re: Order by (for 15 rows) adds 30 seconds to query time - Mailing list pgsql-performance
From | Richard Neill |
---|---|
Subject | Re: Order by (for 15 rows) adds 30 seconds to query time |
Date | |
Msg-id | 4B16F27F.2020005@cam.ac.uk Whole thread Raw |
In response to | Re: Order by (for 15 rows) adds 30 seconds to query time ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
Responses |
Re: Order by (for 15 rows) adds 30 seconds to query
time
|
List | pgsql-performance |
Kevin Grittner wrote: > Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> That does look weird. Do we have a self-contained test case? Not at the moment. It seems to only occur with relatively complex joins. > > Richard, could you capture the schema for the affected tables and > views with pg_dump -s and also the related rows from pg_statistic? > (The actual table contents aren't needed to see this issue.) > Here are the relevant parts of the schema - I've cut this out of the source-tree rather than pg_dump, since it seems more readable. Regarding pg_statistic, I don't understand how to find the relevant rows - what am I looking for? (the pg_statistic table is 247M in size). Thanks for your help, Richard THE PROBLEM QUERY ----------------- SELECT * FROM h.inventory WHERE demand_id = 289276563 ORDER BY id; #Note that using core.inventory (which is simpler) does not have the 30-second problem. #In general the h namespace is intended to be a human-readable diagnostic version whereas the core namespace #is optimised for the application h.inventory and core.inventory ------------------------------ -- -- Inventory -- CREATE TABLE core.inventory ( id bigint NOT NULL DEFAULT core.new_id(), material_id bigint NOT NULL, location_id bigint NOT NULL, qty integer NOT NULL, divergence integer NOT NULL DEFAULT 0, ctime timestamp with time zone NOT NULL DEFAULT now(), actor_id bigint NULL, demand_id bigint NULL, PRIMARY KEY ( id ), FOREIGN KEY ( material_id ) REFERENCES core.__material_id ( id ), FOREIGN KEY ( location_id ) REFERENCES core.__location_id ( id ), FOREIGN KEY ( actor_id ) REFERENCES core.actor ( id ), FOREIGN KEY ( demand_id ) REFERENCES core.demand ( id ) ); CREATE INDEX inventory_material_id ON core.inventory ( material_id ); CREATE INDEX inventory_location_id ON core.inventory ( location_id ); CREATE INDEX inventory_actor_id ON core.inventory ( actor_id ); CREATE INDEX inventory_demand_id ON core.inventory ( demand_id ); CREATE OR REPLACE VIEW h.inventory AS SELECT core.inventory.id, core.inventory.material_id, h_material.tag AS material_tag, h_material.name AS material_name, core.inventory.location_id, h_location.tag AS location_tag, h_location.name AS location_name, core.inventory.qty, core.inventory.divergence, core.inventory.ctime, core.inventory.actor_id, h_actor.tag AS actor_tag, h_actor.name AS actor_name, core.inventory.demand_id, h_demand.target_id, h_demand.target_tag, h_demand.target_name FROM core.inventory LEFT OUTER JOIN h.material AS h_material ON core.inventory.material_id = h_material.id LEFT OUTER JOIN h.location AS h_location ON core.inventory.location_id = h_location.id LEFT OUTER JOIN h.actor AS h_actor ON core.inventory.actor_id = h_actor.id LEFT OUTER JOIN h.demand AS h_demand ON core.inventory.demand_id = h_demand.id; h.material and core.material ---------------------------- -- -- Weights, dimensions, and other material data -- -- -- Materials -- CREATE TABLE core.material ( LIKE core.tag INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES, mass integer NOT NULL CHECK ( mass >= 0 ), volume integer NOT NULL CHECK ( volume >= 0 ) ) INHERITS ( core.tag ); SELECT core.inherit_unique_index ( 'material', 'tag', 'id' ); SELECT core.inherit_unique_index ( 'material', 'tag', 'tag' ); SELECT core.create_inheritable_unique_index ( 'material', 'id', 'bigint' ); COMMENT ON COLUMN core.material.mass IS 'Mass in grams'; COMMENT ON COLUMN core.material.volume IS 'Volume in ml'; CREATE OR REPLACE VIEW h.material AS SELECT core.material.id, core.material.tag, core.material.name, core.material.mass, core.material.volume FROM core.material; h.location and core.location ---------------------------- -- -- Locations -- CREATE TABLE core.location ( LIKE core.tag INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ) INHERITS ( core.tag ); SELECT core.inherit_unique_index ( 'location', 'tag', 'id' ); SELECT core.inherit_unique_index ( 'location', 'tag', 'tag' ); SELECT core.create_inheritable_unique_index ( 'location', 'id', 'bigint' ); CREATE OR REPLACE VIEW h.location AS SELECT core.location.id, core.location.tag, core.location.name FROM core.location; h.actor and core.actor ---------------------- -- -- Actors -- CREATE TABLE core.actor ( LIKE core.tag INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ) INHERITS ( core.tag ); SELECT core.inherit_unique_index ( 'actor', 'tag', 'id' ); SELECT core.inherit_unique_index ( 'actor', 'tag', 'tag' ); CREATE OR REPLACE VIEW h.actor AS SELECT core.actor.id, core.actor.tag, core.actor.name FROM core.actor; h.demand and core.demand ------------------------- -- -- Demand -- CREATE TABLE core.demand ( id bigint NOT NULL DEFAULT core.new_id(), target_id bigint NOT NULL, material_id bigint NOT NULL, qty integer NOT NULL, -- HACK benefit integer NOT NULL DEFAULT 0, PRIMARY KEY ( id ), UNIQUE ( target_id, material_id ), FOREIGN KEY ( target_id ) REFERENCES core.waypoint ( id ), FOREIGN KEY ( material_id ) REFERENCES core.__material_id ( id ) ); CREATE INDEX demand_target_id ON core.demand ( target_id ); CREATE INDEX demand_material_id ON core.demand ( material_id ); CREATE OR REPLACE VIEW h.demand AS SELECT core.demand.id, core.demand.target_id, h_target_waypoint.tag AS target_tag, h_target_waypoint.name AS target_name, core.demand.material_id, h_material.tag AS material_tag, h_material.name AS material_name, core.demand.qty, core.demand.benefit FROM core.demand LEFT OUTER JOIN h.waypoint AS h_target_waypoint ON core.demand.target_id = h_target_waypoint.id LEFT OUTER JOIN h.material AS h_material ON core.demand.material_id = h_material.id; h.waypoint and core.waypoint ---------------------------- -- -- Waypoints -- CREATE TABLE core.waypoint ( LIKE core.location INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES, is_router boolean NOT NULL, is_target boolean NOT NULL, is_packer boolean NOT NULL ) INHERITS ( core.location ); SELECT core.inherit_unique_index ( 'waypoint', 'tag', 'id' ); SELECT core.inherit_unique_index ( 'waypoint', 'tag', 'tag' ); SELECT core.inherit_unique_index ( 'waypoint', 'location', 'id' ); COMMENT ON COLUMN core.waypoint.is_router IS 'Routing decisions may be made at this waypoint'; COMMENT ON COLUMN core.waypoint.is_target IS 'Routing may be aimed towards this waypoint'; COMMENT ON COLUMN core.waypoint.is_packer IS 'Containerisation takes place at this waypoint'; CREATE OR REPLACE VIEW h.waypoint AS SELECT core.waypoint.id, core.waypoint.tag, core.waypoint.name, core.waypoint.is_router, core.waypoint.is_target, core.waypoint.is_packer FROM core.waypoint;
pgsql-performance by date: