Query is slower when fetching all data

Question

I have 2 tables:

CREATE TABLE logs (
    user_id int4 NOT NULL,
    elements int4 NULL,
    url varchar(2048) NULL,
    code int4 NULL,
    message varchar(10) NULL,
    status_time timestamp NULL,
    vps_id int4 NULL,
    type_id int4 NULL,
);

CREATE TABLE users (
    user_id int4 NOT NULL,
    account_id int4 NOT NULL
);

And 3 indexes:

CREATE INDEX users_account_id_idx   ON users    USING btree (account_id);
CREATE INDEX users_id_idx           ON users    USING btree (user_id);
CREATE INDEX logs_user_idx          ON logs     USING btree (user_id);

With this amount of data:

select count(*) from logs

| count     |
|-----------|
| 215034554 |

------------------------

select count(*) 
from users

|count|
|-----|
|113  |

And this distribution of the data:

select account_id, count(*)
from users
group by account_id 
order by count(*) desc

|account_id|count|
|----------|-----|
|58,174    |15   |
|78,231    |11   |
|70,641    |9    |
|74,494    |6    |
|74,493    |6    |
|60,829    |5    |
|75,008    |5    |
|76,300    |4    |
|77,169    |4    |
|77,594    |4    |
|63,998    |3    |
|75,433    |3    |
|74,479    |3    |
|75,536    |3    |
|71,728    |3    |
|77,389    |3    |
|78,257    |2    |
|77,866    |2    |
|66,913    |2    |
|79,009    |2    |
|66,364    |2    |
|76,883    |1    |
|77,161    |1    |
|75,524    |1    |
|76,529    |1    |
|76,388    |1    |
|76,688    |1    |
|75,308    |1    |
|78,074    |1    |
|77,045    |1    |
|75,758    |1    |
|79,075    |1    |
|74,827    |1    |
|75,484    |1    |
|77,573    |1    |
|76,079    |1    |
|76,003    |1    |

------------------------

select user_id, count(*)
from logs
group by user_id
order by count(*) desc

|user_id|count     |
|-------|----------|
|145    |95,815,778|
|257    |25,574,680|
|255    |17,013,301|
|310    |14,986,006|
|254    |14,701,714|
|292    |13,147,405|
|150    |7,181,137 |
|247    |6,930,556 |
|403    |4,260,893 |
|293    |3,702,963 |
|275    |3,215,303 |
|253    |2,648,267 |
|184    |1,197,320 |
|221    |802,206   |
|309    |709,847   |
|308    |661,653   |
|260    |628,120   |
|228    |590,788   |
|438    |433,570   |
|316    |291,468   |
|160    |195,000   |
|264    |158,459   |
|266    |55,986    |
|229    |34,213    |
|419    |21,664    |
|155    |17,407    |
|152    |9,509     |
|258    |7,979     |
|149    |7,451     |
|153    |4,918     |
|156    |4,862     |
|147    |3,155     |
|157    |2,762     |
|93     |2,622     |
|401    |2,169     |
|420    |1,816     |
|273    |1,632     |
|154    |1,416     |
|321    |1,099     |
|231    |1,055     |
|233    |830       |
|146    |612       |
|318    |561       |
|148    |548       |
|334    |501       |
|252    |493       |
|182    |336       |
|326    |248       |
|151    |210       |
|190    |163       |
|392    |138       |
|195    |114       |
|194    |104       |
|232    |102       |
|1      |96        |
|407    |94        |
|193    |93        |
|234    |86        |
|327    |82        |
|198    |77        |
|187    |76        |
|428    |75        |
|439    |62        |
|307    |44        |
|186    |42        |
|83     |41        |
|222    |38        |
|313    |38        |
|270    |34        |
|143    |32        |
|75     |31        |
|395    |28        |
|250    |24        |
|104    |24        |
|180    |22        |
|416    |19        |
|220    |18        |
|406    |16        |
|249    |15        |
|320    |15        |
|394    |14        |
|322    |14        |
|383    |14        |
|332    |14        |
|248    |12        |
|375    |10        |
|28     |10        |
|376    |10        |
|333    |9         |
|177    |8         |
|319    |8         |
|381    |7         |
|207    |6         |
|374    |6         |
|197    |6         |
|251    |5         |
|176    |5         |
|224    |4         |
|166    |4         |
|161    |4         |
|317    |4         |
|178    |4         |
|399    |4         |
|185    |4         |
|191    |4         |
|162    |4         |
|386    |3         |
|169    |3         |
|328    |3         |
|400    |3         |
|201    |2         |
|81     |2         |
|223    |2         |
|196    |2         |
|389    |2         |
|101    |2         |
|94     |2         |
|269    |1         |
|385    |1         |
|398    |1         |

I have a problem with this query:

select l.user_id, l.status_time
from logs l
inner join users u on u.user_id = l.user_id
where u.account_id = 58174

because it works really slow, about 20 second to complete. If I change it to:

select l.user_id ---- change is only there
from logs l
inner join users u on u.user_id = l.user_id
where u.account_id = 58174

it executes in 50 ms, returning 19 rows in total.

Another hack is to prevent inner join, but get the list of total user_id first, and then use it in this query:

select user_id 
from users
where account_id = 58174

(61,71,68,69,70,118,116,117,248,381,384,325,265,393,521)

------------------------

select l.user_id, l.status_time
from logs l
where l.user_id in (61,71,68,69,70,118,116,117,248,381,384,325,265,393,521)

which also returns 19 rows in a few miliseconds..

I also tried changing statistics:

ALTER TABLE logs ALTER COLUMN user_id SET STATISTICS 1500;

but it doesn't help with my problematic query. Do you have any ideas why fetching 2 columns instead 1 is much slower?

Answer 1

Oftentimes a composite index (one that encompasses multiple columns) is necessary to be covering for the query. Your query is an example of such a case. You should minimally have this index instead of your singleton index on only user_id:

CREATE INDEX logs_user_id_status_time
ON logs USING btree (user_id, status_time);

Without it, when you SELECT the status_time column, the entire table (of ~200 million rows) needs to be scanned since your current index doesn't contain the data needed (status_time) to serve your query.

In addition to this, as pointed out by Charlieface, to also make your indexes against the users table covering, it's probably ideal to combine them into a single composite index as well as (account_id, user_id).

---

Finally, as mustaccio pointed out in his comment on your post, this is essentially guesswork (though very likely to be true based on your simple use cases) without seeing the EXPLAIN ANALYZE of both queries.

Answer 2

As others pointed out, pasting the execution plan could be useful.

SOME WILD GUESS

For your problematic query

select l.user_id, l.status_time
from logs l
inner join users u on u.user_id = l.user_id
where u.account_id = 58174

The planner most likely using user_id index on both table to join them together then definitely need to bring all blocks from the heap to have filtering on account_id as well as displaying status_time. It might be spending some time on I/O for heap fetches.

But once the status_time removed,

select l.user_id 
from logs l
inner join users u on u.user_id = l.user_id
where u.account_id = 58174

Here planner may choose to use index on account_id on "users" on table first and there after joining with "logs" which is definitely going to be faster as "users" table have only few entries after filtering. Same flow of execution when you split the queries into two.

I expect below query would be faster.

select l.user_id, l.status_time
from logs l
inner join (select user_id from users where u account_id = 58174)u
on u.user_id = l.user_id

Additionally composite index or covering index on "logs" with both (user_id , status_time) avoid heap fetch and reduce I/O time.