Databases
StackOverflow2013
Postsdbo
POQuery on Postgres totaly holds down server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery on Postgres totaly holds down server
    primarykey
    data
    text
    <p>Now, I'am moving our database from Microsoft SQL Server to PostgreSQL 9.1. There are a simple query, to calculate some summary of our store:</p> <pre><code>SELECT DISTINCT p.part_name_id, (SELECT SUM(p1.quantity) FROM parts.spareparts p1 WHERE p1.part_name_id = p.part_name_id) AS AllQuantity, (SELECT SUM(p2.price * p2.quantity) FROM parts.spareparts p2 WHERE p2.part_name_id = p.part_name_id) AS AllPrice FROM parts.spareparts p </code></pre> <p>It working very fast on MSSQL, less than one second, there are about 150 000 records in <code>spareparts</code> table.</p> <p>In PostgreSQL I waited for 200,000 milliseconds and not wait for the result.</p> <p>Where I was wrong?</p> <p>P.S.: table definitions:</p> <pre><code>-- Table: parts.spareparts -- DROP TABLE parts.spareparts; CREATE TABLE parts.spareparts ( id serial NOT NULL, big_id bigint NOT NULL, part_unique integer NOT NULL, store_address integer, brand_id integer, model_id integer, category_id integer, part_name_id integer, price money, quantity integer, description character varying(250), private_info character varying(600), manager_id integer, company_id integer, part_type smallint, box_number integer, com_person character varying(200), com_phone character varying(200), vendor_id integer, is_publish boolean DEFAULT true, is_comission boolean DEFAULT false, is_new boolean DEFAULT false, is_warning boolean DEFAULT false, catalog_no character varying(200), disc_id integer, is_set boolean, w_height numeric(3,2), w_width numeric(3,2), w_diam numeric(3,2), w_type integer, page_url character varying(150), last_edit_manager_id integer, CONSTRAINT spareparts_pk PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE parts.spareparts OWNER TO asap; -- Index: parts.sparepart_part_unique_idx -- DROP INDEX parts.sparepart_part_unique_idx; CREATE INDEX sparepart_part_unique_idx ON parts.spareparts USING btree (part_unique, company_id); -- Index: parts.spareparts_4param_idx -- DROP INDEX parts.spareparts_4param_idx; CREATE INDEX spareparts_4param_idx ON parts.spareparts USING btree (brand_id, model_id, category_id, part_name_id); -- Index: parts.spareparts_bigid_idx -- DROP INDEX parts.spareparts_bigid_idx; CREATE INDEX spareparts_bigid_idx ON parts.spareparts USING btree (big_id); -- Index: parts.spareparts_brand_id_part_id_quantity_idx -- DROP INDEX parts.spareparts_brand_id_part_id_quantity_idx; CREATE INDEX spareparts_brand_id_part_id_quantity_idx ON parts.spareparts USING btree (brand_id, part_name_id, quantity); -- Index: parts.spareparts_brand_id_quantity_idx -- DROP INDEX parts.spareparts_brand_id_quantity_idx; CREATE INDEX spareparts_brand_id_quantity_idx ON parts.spareparts USING btree (brand_id, quantity); -- Index: parts.spareparts_company_id_part_unique_idx -- DROP INDEX parts.spareparts_company_id_part_unique_idx; CREATE INDEX spareparts_company_id_part_unique_idx ON parts.spareparts USING btree (company_id, part_unique); -- Index: parts.spareparts_model_id_company_id -- DROP INDEX parts.spareparts_model_id_company_id; CREATE INDEX spareparts_model_id_company_id ON parts.spareparts USING btree (model_id, company_id); COMMENT ON INDEX parts.spareparts_model_id_company_id IS 'Для frmFilter'; -- Index: parts.spareparts_url_idx -- DROP INDEX parts.spareparts_url_idx; CREATE INDEX spareparts_url_idx ON parts.spareparts USING btree (page_url COLLATE pg_catalog."default"); -- Trigger: spareparts_delete_trigger on parts.spareparts -- DROP TRIGGER spareparts_delete_trigger ON parts.spareparts; CREATE TRIGGER spareparts_delete_trigger AFTER DELETE ON parts.spareparts FOR EACH ROW EXECUTE PROCEDURE parts.spareparts_delete_fn(); -- Trigger: spareparts_update_trigger on parts.spareparts -- DROP TRIGGER spareparts_update_trigger ON parts.spareparts; CREATE TRIGGER spareparts_update_trigger AFTER INSERT OR UPDATE ON parts.spareparts FOR EACH ROW EXECUTE PROCEDURE parts.spareparts_update_fn(); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndrey
    1. USAndrey
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload