Kevin Grittner wrote:
Tom Lane <tgl@xxxxxxxxxxxxx> 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;
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
