Re: Seq scan instead of index scan querying single row from primary key on large table

From: Andy Fan <zhihuifan1213(at)163(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: James Coleman <jtc331(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Seq scan instead of index scan querying single row from primary key on large table
Date: 2024-07-31 01:05:05
Message-ID: 878qxis75q.fsf@163.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> I'm not sure what to do about this. I think that things might work
> out better if we redefined the startup cost as "estimated cost to
> retrieve the first tuple", rather than its current very-squishy
> definition as "cost to initialize the scan".

Actually I wanted to raise this question very long time ago when I
read the code, but I don't have a example to prove it can cause any real
impact, then I didn't ask it.

startup_cost is defined by the cost to retrieve the *first tuple*, so
for the query like "SELECT * FROM t WHERE foo", the IO cost to retrieve
the first tpule is obviously not 0. (I think it can be total_cost /
rows?) at the same time, the startup_cost of IndexScan is more
restricted, it counts the IO blocks from root -> leaf nodes. I think
there is a inconsistent issue as well.

> That would end up
> with the LIMIT node having a cost that's at least the sum of the
> startup costs of the input scans, which would fix this problem.

great to know this.

> But changing that everywhere would be a lotta work.

In my understanding, the only place we need to change is the
startup_cost in cost_seqscan, I must be wrong now, but I want to know
where is it.

> and I'm far from sure that it would not have any negative
> side-effects.

Yes, I think it is a semantics correct than before however.

--
Best Regards
Andy Fan

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2024-07-31 01:05:18 Re: Popcount optimization using AVX512
Previous Message Andres Freund 2024-07-31 00:49:59 Re: Popcount optimization using AVX512