3

I'm building a statistic application based on Django & PostgreSQL with focus on performance and maintainability.

The problem is, that data is distributed among multiple relatively large tables.

Here is the simplified schema:

CREATE TABLE "items_item" (
    "id"          SERIAL NOT NULL PRIMARY KEY, 
    "country"     VARCHAR(2) NOT NULL, 
    "created"     TIMESTAMP WITH TIME ZONE NOT NULL,
    "city"        VARCHAR(64) NOT NULL
);
CREATE TABLE "payments_companypayment" (
    "id"          SERIAL NOT NULL PRIMARY KEY,
    "created"     TIMESTAMP WITH TIME ZONE NOT NULL, 
    "amount"      NUMERIC(11, 2) NOT NULL, 
    "item_id"     INTEGER NULL
);
CREATE TABLE "payments_affiliatepayment" (
    "id"          SERIAL NOT NULL PRIMARY KEY,
    "created"     TIMESTAMP WITH TIME ZONE NOT NULL, 
    "amount"      NUMERIC(11, 2) NOT NULL, 
    "item_id"     INTEGER NULL
);
CREATE TABLE "extra_conversion" (
    "id"          SERIAL NOT NULL PRIMARY KEY,
    "created"     TIMESTAMP WITH TIME ZONE NOT NULL, 
    "amount"      NUMERIC(11, 2) NOT NULL, 
    "type"        VARCHAR(64) NOT NULL
    "item_id"     INTEGER NULL
);

--- view definition
CREATE OR REPLACE VIEW extra_statistic AS
SELECT
  item_id AS "id",
  COALESCE(
    SUM("extra_conversion"."amount"),
  0) AS "total_conversions",
  COALESCE(
    SUM("extra_conversion"."amount")
      FILTER (WHERE "extra_conversion"."type" = 'BAD'),
  0) AS "bad_conversions",
  COALESCE(
    SUM("extra_conversion"."amount")
      FILTER (WHERE "extra_conversion"."type" = 'GOOD'),
  0) AS "good_conversions"
FROM "extra_conversion"
GROUP BY 1

My reports are similar to this:

SELECT city                                AS "group_by",
COUNT(*)                                   AS "item_count",
COALESCE(SUM(EXTRA.total_conversions), 0)  AS "total_conversions",
COALESCE(SUM(EXTRA.bad_conversions), 0)    AS "bad_conversions",
COALESCE(SUM(EXTRA.good_conversions), 0)   AS "good_conversions"
FROM items_item 
LEFT OUTER JOIN extra_statistic AS EXTRA USING(id)
WHERE items_item.created > '2016-10-01' AND 
      items_item.created < '2016-10-31' AND 
      items_item.country = 'CZ'
GROUP BY 1;

I want to count total number of items, total amount of conversions by city.

Also there is an index on items_item(created, country).

Here is the query plan:

HashAggregate  (cost=149024.40..149151.59 rows=7268 width=112) (actual time=114.754..116.714 rows=5037 loops=1)
   Output: items_item.city, count(*), COALESCE(sum((COALESCE(sum(extra_conversion.amount), '0'::numeric))), '0'::numeric), COALESCE(sum((COALESCE(sum(extra_conversion.amount) FILTER (WHERE ((extra_conversion.type)::text = 'BAD'::text)), '0'::numeric))), '0'::numeric), COALESCE(sum((COALESCE(sum(extra_conversion.amount) FILTER (WHERE ((extra_conversion.type)::text = 'GOOD'::text)), '0'::numeric))), '0'::numeric)
   Group Key: items_item.city
   ->  Hash Right Join  (cost=147824.04..148129.11 rows=71623 width=104) (actual time=85.799..100.339 rows=45331 loops=1)
         Output: items_item.city, (COALESCE(sum(extra_conversion.amount), '0'::numeric)), (COALESCE(sum(extra_conversion.amount) FILTER (WHERE ((extra_conversion.type)::text = 'BAD'::text)), '0'::numeric)), (COALESCE(sum(extra_conversion.amount) FILTER (WHERE ((extra_conversion.type)::text = 'GOOD'::text)), '0'::numeric))
         Hash Cond: (extra_conversion.item_id = items_item.id)
         ->  HashAggregate  (cost=2449.43..2618.28 rows=9649 width=112) (actual time=29.256..34.217 rows=10439 loops=1)
               Output: extra_conversion.item_id, COALESCE(sum(extra_conversion.amount), '0'::numeric), COALESCE(sum(extra_conversion.amount) FILTER (WHERE ((extra_conversion.type)::text = 'BAD'::text)), '0'::numeric), COALESCE(sum(extra_conversion.amount) FILTER (WHERE ((extra_conversion.type)::text = 'GOOD'::text)), '0'::numeric)
               Group Key: extra_conversion.item_id
               ->  Seq Scan on public.extra_conversion  (cost=0.00..1458.57 rows=66057 width=24) (actual time=0.006..3.746 rows=66111 loops=1)
                     Output: extra_conversion.id, extra_conversion.created, extra_conversion.amount, extra_conversion.type, extra_conversion.item_id
         ->  Hash  (cost=144479.33..144479.33 rows=71623 width=24) (actual time=56.301..56.301 rows=45331 loops=1)
               Output: items_item.city, items_item.id
               Buckets: 131072  Batches: 1  Memory Usage: 3568kB
               ->  Bitmap Heap Scan on public.items_item  (cost=4510.48..144479.33 rows=71623 width=24) (actual time=14.320..46.535 rows=45331 loops=1)
                     Output: items_item.city, items_item.id
                     Recheck Cond: ((items_item.created > '2016-10-01 00:00:00+02'::timestamp with time zone) AND (items_item.created < '2016-10-31 00:00:00+01'::timestamp with time zone) AND ((items_item.country)::text = 'CZ'::text))
                     Heap Blocks: exact=20301
                     ->  Bitmap Index Scan on items_item_created_country_idx  (cost=0.00..4492.58 rows=71623 width=0) (actual time=10.659..10.659 rows=45335 loops=1)
                           Index Cond: ((items_item.created > '2016-10-01 00:00:00+02'::timestamp with time zone) AND (items_item.created < '2016-10-31 00:00:00+01'::timestamp with time zone) AND ((items_item.country)::text = 'CZ'::text))
 Planning time: 0.284 ms
 Execution time: 116.932 ms
(22 rows)

Now, I want to introduce aggregation on payments_companypayment and payments_affiliatepayment, but as I see it is not good approach because view extra_statistic aggregates data from whole table, which in my case leads to some performance issues. These tables are growing and have 350k and 2M rows respectively, so it is not good to calculate statistic over whole these large tables on every query.

How can I improve it?

I tried something like:

SELECT city                                           AS "group_by",
COUNT(*)                                              AS "item_count",
COALESCE(SUM(extra_conversion.amount), 0)  AS "total_conversions",
COALESCE(
  SUM("extra_conversion"."amount")
  FILTER (WHERE "extra_conversion"."type" = 'BAD'),
0) AS "bad_conversions",
COALESCE(
  SUM("extra_conversion"."amount")
  FILTER (WHERE "extra_conversion"."type" = 'GOOD'),
0) AS "good_conversions"
FROM items_item 
LEFT OUTER JOIN extra_conversion ON (items_item.id = extra_conversion.item_id)
WHERE items_item.created > '2016-10-01' AND 
      items_item.created < '2016-10-31' AND 
      items_item.country = 'CZ'
GROUP BY 1;

But it fails if single item has multiple conversions - item_count is wrong. I don't know how to solve this.

PostgreSQL version: 9.6.1

Thank you!

Update: I solved the items_item counting problem by using COUNT(DISTINCT items_item.id), but I still thinking about how to improve the query.

-1

First use a count (1) and not count (*) because otherwise all fields from the tables are read from the database. Second create an index on (created,country). Or even better create an index on (created,country,id). If you do this, a index only scan is done on items_item.

  • 2
    The assumption that count(1) is faster then count(*) is plain wrong (and has never been true) – a_horse_with_no_name Nov 8 '16 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.