The item table is like this
id category_id name registered
I have to show total number of items from the beginning. Suppose between 2013-05-01 to 20-05–05, I have to count all the items before 2013-05-01 and add that to count of 2013-05-01 's items. The result have to be grouped by registered and category_id. I have to do this in one query. This is my query
SELECT registered, me.category_id,
COUNT( * ) + (select count(*) from items where me.category_id = category_id
and registered < '2013-05-01 00:00:00') AS Value FROM items me
WHERE (
(
category_id IS NOT NULL
AND
(me.registered BETWEEN '2013-05-01 00:00:00' AND '2013-05-02 00:00:00')
)
)
GROUP BY registered, category_id
It takes almost 28 secs to generate the result. How can i increase the performance.
Update
I am using pgSQL. The registered and category_id is indexed. There are 568814 rows. I will be also very grateful, if any one can tell me that my sql is correct or not.
Update
Sorry for late reply.
CREATE TABLE items
(
id uuid NOT NULL DEFAULT uuid_generate_v4(),
registered timestamp without time zone NOT NULL,
modified timestamp without time zone NOT NULL,
published timestamp without time zone,
owner_id uuid,
status_id integer NOT NULL,
category_id uuid NOT NULL,
location_id uuid,
title text,
description text,
quantity integer NOT NULL DEFAULT 1,
unit text,
price_low bigint,
price bigint,
currency text NOT NULL DEFAULT 'BDT'::text,
ip text,
attributes_json text,
from_mobile boolean NOT NULL DEFAULT false,
CONSTRAINT items_pkey PRIMARY KEY (id),
CONSTRAINT items_fk_category_id FOREIGN KEY (category_id)
REFERENCES categories (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_location_id FOREIGN KEY (location_id)
REFERENCES locations (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_owner_id FOREIGN KEY (owner_id)
REFERENCES users (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_status_id FOREIGN KEY (status_id)
REFERENCES item_statuses (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
Best Answer
What kind of column is [registered] -> datetime or varchar
Why are you grouping by the full value of the [registered] column ?
Ex. If you have the following values for this column:
... and you group by them, you will have 5 rows in the output.
Grouping at the minute or hour level will get you less results -> faster query execution time ... if you have 568.814 rows, how many rows does your query return ? I suppose the result set is very close to 568.814 rows :)
In the current form, for each row generated by the primary query, the subquery has to be run. Try to avoid that ...
Here my version of your query. Based on the same input, the output will be the same:
Output
Query Plan
For anyone interested, here is the sql fiddle page with sample data: SQL-Fiddle-2