Yep, you're right, Andrew, adding a couple rows made it do the index
only scan. I reckon I got misled by turning off sequential scans,
thinking that actual rows were not important anymore. Overly simplistic
reasonings can get one into trouble, lol.
Regards,
Michael Vitale
Andrew Gierth
On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote:
> With seqscan disabled, I get this plan on 9.6:
> Bitmap Heap Scan on friend (cost=8.42..19.01 rows=14 width=8)
...
> I expected to get an index-only scan in this situation, as that would be a
> very common query. Is there a way to
Yikes, apologies to all, my wording is the opposite of what I meant!
Index only scans are preferred for infrequently updated ones, not
heavily updated ones where the visibility map is updated often.
Regards,
Michael Vitale
MichaelDBA wrote on 10/12/2019 11:27 AM:
Another thing to consider
Nope, vacuumed it and still got the bitmap index scans.
Andrew Gierth wrote on 10/12/2019 11:33 AM:
"MichaelDBA" == MichaelDBA writes:
MichaelDBA> BTW, to Andrew, the UNION ALL alternative still results in
MichaelDBA> bitmap index scans from my testing.
You probably forgot to vacuum the
> "MichaelDBA" == MichaelDBA writes:
MichaelDBA> BTW, to Andrew, the UNION ALL alternative still results in
MichaelDBA> bitmap index scans from my testing.
You probably forgot to vacuum the table.
--
Andrew (irc:RhodiumToad)
> "MichaelDBA" == MichaelDBA writes:
MichaelDBA> Nope, vacuumed it and still got the bitmap index scans.
Let's see your explains. Here's mine:
# set enable_seqscan=false; -- because I only have a few rows
SET
# insert into friend values (1,2),(2,5);
INSERT 0 2
# vacuum analyze friend;
> "MichaelDBA" == MichaelDBA writes:
MichaelDBA> Yep, you're right, Andrew, adding a couple rows made it do
MichaelDBA> the index only scan. I reckon I got misled by turning off
MichaelDBA> sequential scans, thinking that actual rows were not
MichaelDBA> important anymore. Overly
> "Ivan" == Ivan Voras writes:
Ivan> Hello,
Ivan> There's a "users" table with the following structure:
Ivan> CREATE TABLE "user" (
Ivan> id SERIAL PRIMARY KEY,
Ivan> -- other fields
Ivan> );
Ivan> and there's a "friends" table with the following structure:
Ivan> CREATE TABLE
On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby wrote:
> On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote:
> > With seqscan disabled, I get this plan on 9.6:
> > Bitmap Heap Scan on friend (cost=8.42..19.01 rows=14 width=8)
> ...
> > I expected to get an index-only scan in this
Another thing to consider is the visibility map. From what I
understand, index only scans are preferred for heavily updated tables,
not infrequently updated ones. Even though index only scans imply ONLY
they really aren't in the sense that they may need to visit the
Visibility Map for the
Perhaps the fix by Alexander Kuzmenkov in V11 added this VM
consideration for having a preference of bitmap index scan over an index
only scan. Looks like I'm goin' down the rabbit hole...
Regards,
Michael Vitale
MichaelDBA wrote on 10/12/2019 11:35 AM:
Nope, vacuumed it and still got the
11 matches
Mail list logo