From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Surprised by index choice for count(*) |
Date: | 2018-05-01 15:46:51 |
Message-ID: | 38fc1d14-1207-cbca-18e0-b2e6f269565b@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Should I be? I would have thought the pk would have been chosen v.
function index?
explain analyse select count(*) from bc.segment s;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=4428009.24..4428009.25 rows=1 width=8)
(actual time=14786.395..14786.395 rows=1 loops=1)
-> Gather (cost=4428009.03..4428009.24 rows=2 width=8) (actual
time=14786.358..14786.386 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=4427009.03..4427009.04 rows=1
width=8) (actual time=14782.167..14782.167 rows=1 loops=3)
-> Parallel Index Only Scan using fpv on segment s
(cost=0.57..4210177.14 rows=86732753 width=0) (actual
time=0.061..11352.855 rows=69386204 loops=3)
Heap Fetches: 1780
Planning time: 0.221 ms
Execution time: 14815.939 ms
(9 rows)
\d bc.segment
Table "bc.segment"
Column | Type | Modifiers
----------------+---------+--------------------
id | uuid | not null
chrom | integer | not null
markerset_id | uuid | not null
probandset_id | uuid | not null
startbase | integer | not null
endbase | integer | not null
firstmarker | integer | not null
lastmarker | integer | not null
events_less | bigint | not null default 0
events_equal | bigint | not null default 0
events_greater | bigint | not null default 0
Indexes:
"segment_pkey" PRIMARY KEY, btree (id)
"useg" UNIQUE, btree (probandset_id, chrom, startbase, endbase)
"fpv" btree (pv(events_less, events_equal, events_greater, 0))
"segment_markerset_id_probandset_id_idx" btree (markerset_id,
probandset_id)
create or replace function public.pv(l bigint, e bigint, g bigint, o int)
returns float
as
$$
select 1.0*(g+e+o)/(l+e+g+o)::float;
$$
language sql
;
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-05-01 16:07:49 | Re: Surprised by index choice for count(*) |
Previous Message | Merlin Moncure | 2018-05-01 13:53:28 | Re: Locks analysis after-the-fact |