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

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;