0

I'm using PostgreSQL 9.6.6, and have a table with 3.6 million records. its structure is

CREATE TABLE public.datasets (
    id integer NOT NULL,
    address_locality_id uuid,
    subject_id uuid NOT NULL
);

id was created as SERIAL and is the primary key. address_locality_id has a btree index in the form

CREATE INDEX dataset_address_locality_id_idx ON dataset USING btree (address_locality_id)

When I query:

SELECT address_locality_id, count(*) 
FROM dataset
GROUP BY address_locality_id;

It can take over 30 seconds.

Here is the output of EXPLAIN (ANALYZE, BUFFERS)

Finalize GroupAggregate  (cost=198446.49..198448.57 rows=52 width=24) (actual time=34013.918..34015.753 rows=53 loops=1)

  Group Key: address_locality_id

  Buffers: shared hit=799 read=183798 dirtied=153798 written=3088

  ->  Sort  (cost=198446.49..198447.01 rows=208 width=24) (actual time=34013.908..34015.568 rows=265 loops=1)

        Sort Key: address_locality_id

        Sort Method: quicksort  Memory: 45kB

        Buffers: shared hit=799 read=183798 dirtied=153798 written=3088

        ->  Gather  (cost=198417.16..198438.48 rows=208 width=24) (actual time=34012.797..34013.677 rows=265 loops=1)

              Workers Planned: 4

              Workers Launched: 4

              Buffers: shared hit=796 read=183798 dirtied=153798 written=3088

              ->  Partial HashAggregate  (cost=197417.16..197417.68 rows=52 width=24) (actual time=34009.934..34009.967 rows=53 loops=5)

                    Group Key: address_locality_id

                    Buffers: shared read=183798 dirtied=153798 written=3088

                    ->  Parallel Seq Scan on dataset  (cost=0.00..192877.44 rows=907944 width=16) (actual time=0.727..33322.634 rows=726408 loops=5)

                          Buffers: shared read=183798 dirtied=153798 written=3088

Planning time: 3.583 ms
Execution time: 34030.121 ms

I have been able to reduce the query time using transaction isolation:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
EXPLAIN (ANALYZE, BUFFERS)
select address_locality_id, count(*) 
from dataset
group by address_locality_id;

Which yields:

HashAggregate  (cost=84747.62..84748.14 rows=52 width=24) (actual time=2386.293..2386.308 rows=53 loops=1)
  Group Key: address_locality_id
  Buffers: shared hit=30267
  ->  Seq Scan on dataset  (cost=0.00..66587.41 rows=3632041 width=16) (actual time=0.013..905.979 rows=3632041 loops=1)
        Buffers: shared hit=30267
Planning time: 0.218 ms
Execution time: 2386.388 ms

Why is the first query using GroupAggregate and nested Sort -> Gather -> Partial HashAggregate whereas the second one has a much simpler plan?

Is correct to use BTREE indexes for COUNT?

EDIT:

Eventhough it was a freshly created table, I followed the advise to run VACUUM ANALYZE on the table and the query became much faster. Still has the same nested operations:

Finalize GroupAggregate  (cost=44916.48..44918.56 rows=52 width=24) (actual time=1847.197..1847.353 rows=53 loops=1)

  Group Key: address_locality_id

  Buffers: shared hit=687 read=30267

  ->  Sort  (cost=44916.48..44917.00 rows=208 width=24) (actual time=1847.190..1847.239 rows=265 loops=1)

        Sort Key: address_locality_id

        Sort Method: quicksort  Memory: 45kB

        Buffers: shared hit=687 read=30267

        ->  Gather  (cost=44887.15..44908.47 rows=208 width=24) (actual time=1843.408..1846.976 rows=265 loops=1)

              Workers Planned: 4

              Workers Launched: 4

              Buffers: shared hit=684 read=30267

              ->  Partial HashAggregate  (cost=43887.15..43887.67 rows=52 width=24) (actual time=1840.709..1840.737 rows=53 loops=5)

                    Group Key: address_locality_id

                    Buffers: shared read=30267

                    ->  Parallel Seq Scan on datasetx  (cost=0.00..39347.10 rows=908010 width=16) (actual time=0.303..1340.768 rows=726408 loops=5)

                          Buffers: shared read=30267

Planning time: 4.575 ms
Execution time: 1847.488 ms

It seems this at least solves the issue of speeding things up!

  • Those queries do not seem to have been run against the same database, or the data has drastically changed between runs. Also, the first one probably needs to be vacuumed. – jjanes Mar 21 '18 at 18:21
  • After the table has been through VACUUM ANALYZE, the first EXPLAIN ANALYZE is much faster but its query plan is still the same. Even without vacuum, the second one is much simpler and faster, I cannot see why. – amenadiel Mar 22 '18 at 10:47
  • Can you update the plans in your post so that both are collected when run against recently vacuumed data? – jjanes Mar 22 '18 at 18:48
  • @jjanes I followed your advise and it flies now. I still don't understand why it has such a complicated plan vs the query with transaction isolation. – amenadiel Mar 23 '18 at 11:29
  • The complicated plan is using parallel execution, a rather new feature to postgresql. It is more complicated because it has to spawn worker processes, then gather up the results of them and merge them together to get the global results. postgresql.org/docs/9.6/static/how-parallel-query-works.html – jjanes Mar 23 '18 at 18:55

Your Answer

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

Browse other questions tagged or ask your own question.