3

Schema:

  CREATE TABLE "applications" (
  "id"             SERIAL                   NOT NULL PRIMARY KEY,
  "country"        VARCHAR(2)               NOT NULL,
  "created"        TIMESTAMP WITH TIME ZONE NOT NULL,
  "is_preliminary" BOOLEAN                  NOT NULL,
  "first_name"     VARCHAR(128)             NOT NULL,
  "last_name"      VARCHAR(128)             NOT NULL,
  "birth_number"   VARCHAR(11)              NULL
);

CREATE TABLE "persons" (
  "id"       UUID                     NOT NULL PRIMARY KEY,
  "created"  TIMESTAMP WITH TIME ZONE NOT NULL,
  "modified" TIMESTAMP WITH TIME ZONE NOT NULL
);

ALTER TABLE "applications" ADD COLUMN "physical_person_id" UUID NULL;
CREATE INDEX "physical_person_id_idx" ON "applications" ("physical_person_id");

ALTER TABLE "applications" ADD CONSTRAINT "physical_person_id_fk" FOREIGN KEY ("physical_person_id") REFERENCES "persons" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "country_created" ON "applications" (country, created);

Notes: Value of persons.created should be the same as first application.created for this person, regardless the is_preliminary value.

Query:

SELECT
  to_char(created, 'YYYY-MM-DD') AS "Date",
  COUNT(*) AS "Total",
  COALESCE(
    COUNT(*) FILTER(
      WHERE applications.is_preliminary = false
      AND NOT EXISTS(
        SELECT 1
        FROM applications A
        WHERE A.physical_person_id = applications.physical_person_id
          AND A.created < applications.created
        LIMIT 1
      )
    )
    , 0
  ) AS "Is first app"
FROM applications
WHERE
  created >= '2017-01-01'::TIMESTAMP AND created < '2017-07-01'::TIMESTAMP
  AND country = 'CZ'
GROUP BY 1
ORDER BY 1

Goal: My goal is to see total number of applications vs number of first applications per day in particular country. By first application I mean a number of applications on particular day, which were first time registered and had no applications before.

Problem: Query performance. The number of rows is growing and the performance now is not on the a good level.

Data sample: Here (xz compressed output of pg_dump)

The following query plans are taken from my laptop (on production there was no "external merge")

Query plan:

 GroupAggregate  (cost=54186.11..2391221.59 rows=186832 width=48) (actual time=2137.029..3224.937 rows=181 loops=1)
   Group Key: (to_char(applications.created, 'YYYY-MM-DD'::text))
   ->  Sort  (cost=54186.11..54653.19 rows=186832 width=57) (actual time=2128.554..2370.798 rows=186589 loops=1)
         Sort Key: (to_char(applications.created, 'YYYY-MM-DD'::text))
         Sort Method: external merge  Disk: 8176kB
         ->  Bitmap Heap Scan on applications  (cost=5262.54..30803.18 rows=186832 width=57) (actual time=93.993..411.096 rows=186589 loops=1)
               Recheck Cond: (((country)::text = 'CZ'::text) AND (created >= '2017-01-01 00:00:00'::timestamp without time zone) AND (created < '2017-07-01 00:00:00'::timestamp without time zone))
               Heap Blocks: exact=19640
               ->  Bitmap Index Scan on country_created  (cost=0.00..5215.83 rows=186832 width=0) (actual time=90.945..90.945 rows=186589 loops=1)
                     Index Cond: (((country)::text = 'CZ'::text) AND (created >= '2017-01-01 00:00:00'::timestamp without time zone) AND (created < '2017-07-01 00:00:00'::timestamp without time zone))
   SubPlan 1
     ->  Index Scan using physical_person_id_idx on applications a  (cost=0.43..72.77 rows=6 width=0) (actual time=0.006..0.006 rows=1 loops=127558)
           Index Cond: (physical_person_id = applications.physical_person_id)
           Filter: (created < applications.created)
           Rows Removed by Filter: 0
 Planning time: 0.235 ms
 Execution time: 3261.530 ms

Question: How can I improve the query performance? I suppose, that it could be possible to get rid of subquery in "Is first app", but I don't know how.

PostgreSQL version: 9.6.3

Query plan after update from Evan Carroll:

    Subquery Scan on t  (cost=51624.73..2390836.50 rows=186782 width=52) (actual time=291.726..1129.435 rows=181 loops=1)
 ->  GroupAggregate  (cost=51624.73..2388034.77 rows=186782 width=20) (actual time=291.707..1128.057 rows=181 loops=1)
       Group Key: ((applications.created)::date)
       ->  Sort  (cost=51624.73..52091.69 rows=186782 width=29) (actual time=280.283..334.391 rows=186589 loops=1)
             Sort Key: ((applications.created)::date)
             Sort Method: external merge  Disk: 6720kB
             ->  Bitmap Heap Scan on applications  (cost=5261.90..30801.54 rows=186782 width=29) (actual time=42.944..181.325 rows=186589 loops=1)
                   Recheck Cond: (((country)::text = 'CZ'::text) AND (created >= '2017-01-01 00:00:00+01'::timestamp with time zone) AND (created <= '2017-07-01 00:00:00+02'::timestamp with time zone))
                   Heap Blocks: exact=19640
                   ->  Bitmap Index Scan on country_created  (cost=0.00..5215.20 rows=186782 width=0) (actual time=40.003..40.003 rows=186589 loops=1)
                         Index Cond: (((country)::text = 'CZ'::text) AND (created >= '2017-01-01 00:00:00+01'::timestamp with time zone) AND (created <= '2017-07-01 00:00:00+02'::timestamp with time zone))
       SubPlan 1
         ->  Index Scan using physical_person_id_idx on applications a  (cost=0.43..72.77 rows=6 width=0) (actual time=0.006..0.006 rows=1 loops=127558)
               Index Cond: (physical_person_id = applications.physical_person_id)
               Filter: (created < applications.created)
               Rows Removed by Filter: 0
Planning time: 0.232 ms
Execution time: 1145.761 ms

Initial query without is_first_app column takes ~300 ms.

Query plan for an alternative solution from Erwin Brandstetter:

 GroupAggregate  (cost=51356.14..55562.83 rows=186964 width=20) (actual time=562.470..620.993 rows=181 loops=1)
   Group Key: ((a.created)::date)
   Buffers: shared hit=2137 read=4491, temp read=2491 written=2485
   ->  Sort  (cost=51356.14..51823.55 rows=186964 width=20) (actual time=562.216..592.226 rows=186589 loops=1)
         Sort Key: ((a.created)::date)
         Sort Method: external merge  Disk: 2640kB
         Buffers: shared hit=2137 read=4491, temp read=2491 written=2485
         ->  Hash Right Join  (cost=13394.71..31149.19 rows=186964 width=20) (actual time=119.488..464.407 rows=186589 loops=1)
               Hash Cond: ((p.id = a.physical_person_id) AND (p.created = a.created))
               Join Filter: (NOT a.is_preliminary)
               Buffers: shared hit=2137 read=4491, temp read=2159 written=2153
               ->  Seq Scan on persons p  (cost=0.00..9003.04 rows=364404 width=24) (actual time=3.800..73.486 rows=364404 loops=1)
                     Buffers: shared hit=868 read=4491
               ->  Hash  (cost=9311.25..9311.25 rows=186964 width=25) (actual time=115.213..115.213 rows=186589 loops=1)
                     Buckets: 65536  Batches: 4  Memory Usage: 2875kB
                     Buffers: shared hit=1269, temp written=681
                     ->  Index Only Scan using app_country_created_person_preliminary_idx on applications a  (cost=0.56..9311.25 rows=186964 width=25) (actual time=0.054..64.392 rows=186589 loops=1)
reated < '2017-07-01 00:00:00+02'::timestamp with time zone))
                           Heap Fetches: 0
                           Buffers: shared hit=1269
 Planning time: 0.401 ms
 Execution time: 628.100 ms
Stranger6667
  • 475
  • 1
  • 6
  • 15

3 Answers3

6

Several minor improvements:

SELECT created::date AS the_date
     , COUNT(*) AS total
     , COUNT(*) FILTER( WHERE is_preliminary = false
                        AND   NOT EXISTS (
                           SELECT 1
                           FROM   applications
                           WHERE  physical_person_id = a.physical_person_id
                           AND    created < a.created
                        -- AND    created < a.created::date  -- alternative? see below
                        -- AND    is_preliminary = false     -- omission? see below
                        -- AND    country = 'CZ'             -- not sure. see below
                           LIMIT  1
                           )
                        ) AS is_first_app
FROM   applications a
WHERE  created >= '2017-01-01'::timestamptz
AND    created <  '2017-07-01'::timestamptz
AND    country = 'CZ'
GROUP  BY created::date
ORDER  BY created::date;

  • COALESCE( count(...), 0) is always redundant noise since count() never returns NULL to begin with. Just remove it. Related:

  • The way you had it, you group and sort by the text representation of your timestamptz column created, which happens to work just fine. But it's more expensive than grouping and sorting by an actual date (4-byte integer value internally). Sorting by an actual date or timestamp is also typically more reliable, though it does not make any difference in this particular query. The simplest way to achieve this is a plain cast to date: created::date. You can still format the output if you desire: to_char(created::date, 'YYYY-MM-DD') AS date. Same result, but since we GROUP BY created::date, you have to repeat the grouped expression.

  • Do not use BETWEEN like has been advised. Your filter with >= and < is superior. BETWEEN would translate to >= and <=, which leads to ugly corner cases with fractional values in timestamp (or timestamptz). But since the data type of the underlying column is timestamptz, cast to timestamptz directly. Same result, just one less cast operation:

    WHERE  created >= '2017-01-01'::timestamptz
AND    created <  '2017-07-01'::timestamptz

  • You are aware that the date derived from a timestamptz value (as well as the cast to timestamptz without specifying a time zone) always depend on the current time zone setting, right? You can place the query in a selected time zone explicitly if you want to eliminate this sneaky error source. Basics:

  • There may be logic error(s) in the computation of is_first_app. This is just speculation on my side, though: You are checking whether any row in applications for the same person predates the current one. But while you only allow is_preliminary = false for the current row, you do not enforce the same predicate for the rows to compare to. Typically you would want to compare to rows that are also is_preliminary = false. I added a commented line in the query above.

    Also, since you form groups per day, do you really want to count rows that have a previous entry on the same day, too? Maybe so, but maybe you really want to check for rows predating the day with created < a.created::date.

    Finally, even less sure about that one, you may want to repeat the predicate AND country = 'CZ' to restrict comparison to the same country. I don't have enough information to say more.

  • I also shortened the syntax by trimming noise double quotes (all identifiers are legal anyway) and using a strategic table alias (applications a) in the outer SELECT.

Indices

Since you are concerned with optimizing read performance ...

Your multicolumn index country_created seems ideal for the outer SELECT. But read on ...

But you can easily improve the EXISTS subquery with another multicolumn index:

CREATE INDEX app_person_created_idx ON applications (physical_person_id, created);

To allow index-only scans (only if your write patterns allow it!):

CREATE INDEX app_country_created_person_preliminary_idx
ON applications (country, created, physical_person_id, is_preliminary);

The appended columns physical_person_id and is_preliminary only make sense if you get index-only scans out of it.

I get two index-only scans after adding the last index, which is massively faster for big tables.

More on index-only scans:

Alternative solution

Your last comment opens up new options:

when the application is created for the first time a new person is also created with the same created value.

(The earlier statement in the question was too ambiguous to work with it.)

If this is enforced reliably (and created is never updated in either table), there is a simpler, faster query which also happens to "avoid a subquery in the FILTER clause" - by using a LEFT [OUTER] JOIN instead:

SELECT a.created::date AS date
     , COUNT(*)        AS total
     , COUNT(p.id)     AS is_first_app  -- count only counts non-null values
FROM   applications a
LEFT   JOIN persons p ON a.is_preliminary = false
                     AND p.id = a.physical_person_id  -- FK enforces max. 1 match
                     AND p.created = a.created
WHERE  a.created >= '2017-01-01'::timestamptz
AND    a.created <  '2017-07-01'::timestamptz
AND    a.country = 'CZ'
GROUP  BY a.created::date
ORDER  BY a.created::date;

For perfect read performance with two index-only scans you'd have the index app_country_created_person_preliminary_idx from above. Plus this one on persons:

CREATE INDEX pers_id_created ON persons (id, created);
Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
1

For a few points.

  1. If your type is already timestamp with timezone, then ::timestamp doesn't do anything.
  2. Your range is more cleanly written with BETWEEN.
  3. GROUP BY 1 in your case is actually casting your times to string, and then grouping by that. What you want to do is simply GROUP BY date, and then setting date to stringify in another select if you insist on doing on that in the server (which I wouldn't do anyway).
  4. ORDER BY 1 in your case is actually ordering a list of strings.
  5. All of those varchar above should be text. In PostgreSQL we rarely use varchar. It's text, but slower because of an often-useless length constraint. The only thing not-text that I would have in your schema is the two char country code, I would call it country2char and have it explicitly char(2) but only for verbosity.
  6. Double quotes are really bad practice and are always highly discouraged.

So try this,

SELECT to_char(created, 'YYYY-MM-DD') AS "Date", total AS "Total", is_first_app AS "Is First App"
FROM (
  SELECT
    created::date AS created
    COUNT(*) AS total,
    COALESCE(
      COUNT(*) FILTER(
        WHERE applications.is_preliminary = false
        AND NOT EXISTS(
          SELECT 1
          FROM applications A
          WHERE A.physical_person_id = applications.physical_person_id
            AND A.created < applications.created
          LIMIT 1
        )
      )
      , 0
    ) AS is_first_app
  FROM applications
  WHERE
    created BETWEEN '2017-01-01' AND '2017-07-01'
    AND country = 'CZ'
  GROUP BY 1
) AS t
ORDER BY created;

Now for the subquery, I'll think I would require access to the data to grok and rewrite that one. I can't do it in my head.

See this post for more information

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
0

Not sure if it would perform better, but you could try separating the two count functions, and merging the results:

SELECT "Date"
      ,"Total"
      ,"Is First App"
FROM
       (SELECT
          to_char(created, 'YYYY-MM-DD')     AS "Date",
          COUNT(*) AS "Total",
        FROM
          applications
        WHERE
          created >= '2017-01-01'::TIMESTAMP AND created < '2017-07-01'::TIMESTAMP
          AND country = 'CZ'
        GROUP BY 1
       ) ttl
       LEFT  JOIN 
       (SELECT
          to_char(created, 'YYYY-MM-DD')     AS "Date",
          COUNT(*) AS "Is First App"
        FROM
             (SELECT
                physical_person_id,
                MIN(created) as created
              FROM
                applications
              WHERE
                country = 'CZ'
                AND is_preliminary = false
              GROUP BY 1
             ) fdt -- first date
        WHERE
          created >= '2017-01-01'::TIMESTAMP AND created < '2017-07-01'::TIMESTAMP
        GROUP BY 1
       ) ifa ON (ttl."Date" = ifa."Date")
ORDER BY 1
;

The subquery labeled fdt gets the first application date (where country is 'CZ') for each physical_person_id.

The subquery labeled ifa takes those results, eliminates all rows outside the desired ate range, and gives the count of first applications for each date.

The subquery labeled ttl is your original query, with the "is first app" part removed. LEFT JOIN those results to the ifa results, and you should have want you want.

We don't need to match physical_person_id between ttl and ifa; each count is independent and complete unto itself, so we just need to match dates.

Obviously, if it takes too long to get the first app for all users, this may actually perform worse than the original query. However, by eliminating the correlated sub-query, we're only looking for those first apps once, instead of for each person.

NOTE: code is untested.

Paul White
  • 94,921
  • 30
  • 437
  • 687
RDFozz
  • 11,731
  • 4
  • 25
  • 38