As a followup:
I noticed that there isn't any "pid" column in the reward table. So that
was an error and maybe that's why a sequential scan was being forced. To
recap, the query was:
SELECT
*
FROM
property p
LEFT OUTER JOIN users ON (p.current_owner = users.uid)
WHERE
p.pid in
(SELECT pid FROM reward WHERE reward_type = 'daily'
ORDER BY reward_date DESC LIMIT 30)
But weirdly, the query still runs without any errors. Why ?
===
I tried this (a simpler version of the above):
SELECT * from property
WHERE pid in (
SELECT pid FROM reward WHERE reward_type = 'DAILYPROMO_WIN')
;
And this also runs (again taking a long time) but without any errors.
How can this be ? (there is no pid in the reward table, so why does the
subquery not throw an error)? In fact, because there was no error, I
didn't notice my typo (no pid in reward table) until right now...
Best,
--J