Canan Talayhan <canan.t.talayhan@gmail.com> writes:

> Thanks for your quick response. It helps a lot to me. But still, I
> have some confusion about the reproduction steps. As I understand it,
> I can reproduce the slow query just using the pure SQL queries without
> touching the code for now, right?
>
> Please find my steps below:
>
> 1. CREATE TEMPORARY TABLE temp_package_metadata (LIKE package_metadata
> INCLUDING ALL)
>
> As we expected, it creates a table but has no data. So I want to
> insert some data but colon types are integer and I don't know any
> meaningful data for these colons.

So, the question to ask here is what data would the temporary table
usually contain during loading data for a revision (when the slow query
happens). I gave more specific information about the data in the
temporary table in my previous email.

> 2. CREATE TEMPORARY TABLE temp_package_metadata AS TABLE package_metadata
>
> Then I create a copy of the package_metadata.
>
> 3. EXPLAIN ANALYZE SELECT * FROM temp_package_metadata
> This code generates 155 msec execution time, but I think it should
> take more time.

I don't see why that query should be slower, but that's also not that
relevant since I think the actual slow query in question here is quite
different.

You can find the query in the code by looking at where it does the
timing for "querying the temp_package_metadata".

I've also got information from the PostgreSQL logs, which gives this as
the query:

  SELECT package_metadata.id, package_metadata.home_page, package_metadata.location_id, package_metadata.license_set_id, package_metadata.package_description_set_id, package_metadata.package_synopsis_set_id FROM package_metadata INNER JOIN temp_package_metadata ON (package_metadata.home_page = temp_package_metadata.home_page OR (package_metadata.home_page IS NULL AND temp_package_metadata.home_page IS NULL)) AND (package_metadata.location_id = temp_package_metadata.location_id OR (package_metadata.location_id IS NULL AND temp_package_metadata.location_id IS NULL)) AND (package_metadata.license_set_id = temp_package_metadata.license_set_id OR (package_metadata.license_set_id IS NULL AND temp_package_metadata.license_set_id IS NULL)) AND (package_metadata.package_description_set_id = temp_package_metadata.package_description_set_id OR (package_metadata.package_description_set_id IS NULL AND temp_package_metadata.package_description_set_id IS NULL)) AND (package_metadata.package_synopsis_set_id = temp_package_metadata.package_synopsis_set_id OR (package_metadata.package_synopsis_set_id IS NULL AND temp_package_metadata.package_synopsis_set_id IS NULL))