Re: BUG #18600: Getting wait_type_event as IPC:BTreePage for count queries

On 9/4/24 11:21, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      18600
> Logged by:          Aditya Singh
> Email address:      aditya.singh@lji.io
> PostgreSQL version: 13.11
> Operating system:   macOS
> Description:        
> 
> Table1: 
> Structure: 
> CREATE TABLE "femsa"."activities_bit" (
>     "id" int4 NOT NULL DEFAULT
> nextval('femsa.activities_bit_copy_id_seq'::regclass),
>     "created_ts" timestamptz,
>     "member_id" varchar(40),
>     "bit_reference" varchar(32),
>     "header" jsonb,
>     "payment_details" jsonb,
>     PRIMARY_KEY("id")
> }
> 
> Table 2:
> Structure:
> CREATE TABLE "femsa"."activities_bit" (
>     "id" int8 NOT NULL DEFAULT
> nextval('femsa.activities_bit_copy_id_seq'::regclass),
>     "created_ts" timestamptz,
>     "member_id" varchar(40),
>     "bit_reference" varchar(32),
>     "header" jsonb,
>     "payment_details" jsonb,
>     PRIMARY_KEY("id")
> }
> 
> The only difference between the two is the data type of the id sequence
> (int, bigint).
> 
> SELECT COUNT(*) FROM {table_name} WHERE id >  0;
> When I try to run the above query, I get the result for Table 1, but the
> query on Table 2 goes into wait_type_event IPC:BtreePage. 
> 
> Why this IPC:BtreePage is coming for Table2 when both tables have similar
> structure, data and Indexes?
> 

Not sure I understood the question correctly, but chances are both
queries hit that wait event, but it's intermittent and you only noticed
that for one of them. Or are you saying it gets "stuck" in that wait
even for the table with int8 column?

Another option is that the first query does not use the index at all, in
which case it of course won't see btree wait events. We don't even know
how large the tables are, not to mention which access methods they use.

You'd have to show us explain. I'd bet the first query uses seqscan,
while the second one uses index-only scan.


regards

-- 
Tomas Vondra