>
> Try refactoring to:
>
> select ai.position, i.filename as image_filename, p.filename as
> panorama_filename
> from album_items ai
> left join image2 i on i.imageid = ai.image_id
> left join panoramas p on p.id = ai.panorama_id
> where i.filename in ('pano360--v471.jpg', 'pano360-2--v474.jpg')
>
Hi folks,
I have a fairly simple three-table query (pasted below) with two LEFT JOINs
and an OR in the WHERE clause that for some reason is doing sequential
scans on all three tables (two of them large -- several million rows), even
though I have indexes on the relevant "filename" columns.
Note t
Hmm, weird -- now the RI_Initial_Check() query is much quicker (20s). We do
ANALYZE the data every few nights, so maybe that's what changed it. I'll
keep that in mind. -Ben
On Fri, Nov 1, 2013 at 3:19 AM, Tom Lane wrote:
> Ben Hoyt writes:
> >> It appears the possible
Thanks, Tom (and David and Josh).
> Well, apparently nobody who knows the code was paying attention, because
> that hasn't been true for some time. ALTER TABLE ADD FOREIGN KEY will
> actually validate the constraint using a query constructed like this
> (cf RI_Initial_Check() in ri_triggers.c):
Hi folks,
We're adding a foreign key constraint to a 20-million row table on our
production database, and it's taking about 7 minutes. Because it's an
ALTER TABLE, Postgres acquires an ACCESS EXCLUSIVE lock that prevents
any reads/writes (though this particular table is very write-heavy, so
even a
