542141 - Location bar gets slower after VACUUM (1.5s -> 2s)

Status: RESOLVED INCOMPLETE

(Toolkit :: Places, defect)

Description

[Ed Lee :Mardak]

I was showing dietrich my slow location bar earlier, and we did a vacuum to check if things would change and turns out it got slower. The real reason why it was slow to begin with was that my places.sqlite was "broken". VACUUM did shrink the file size from 31.3MB -> 26MB, but queries were taking longer.

After some inspection, I found my places.sqlite had the wrong index:
CREATE INDEX moz_places_frecencytypedvisitindex ON moz_places (frecency, typed, visit_count);

instead of:
CREATE INDEX moz_places_frecencyindex ON moz_places (frecency);

After DROP INDEX and CREATE, results were coming back instantly, so I'm not sure if after vacuuming it's actually slightly longer than instantly. Maybe somehow my broken places.sqlite prevented sorting on the index? Or maybe the index was just too big to scan quickly?

Comment 1

[Shawn Wilsher :sdwilsh]

I'm more concerned how your index got that way...

Comment 2

[Marco Bonardo [:mak]]

(In reply to comment #1)
> I'm more concerned how your index got that way...

i think that index is a reminder from when he tried to implement bug 412736

Comment 3

[Marco Bonardo [:mak]]

I'm not sure what we can here, I actually suspect vacuum may have caused either a difference with the analyze data that sqlite uses to optimize, or a change in pages positions in the database, the locationbar uses the pages cache quite heavily.
That said, we don't have control over that.