2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-1]  sql_error_code = 00000 time_ms = "2025-12-08 09:04:14.600 UTC" pid="230626" proc_start_time="2025-12-08 09:00:03 UTC" session_id="69369393.384e2" vtid="52/0" tid="0" log_line="4" database="ddvvgu681gm35p" connection_source="10.0.158.34(55468)" user="uc0g389cnbt72e" application_name="[unknown]" LOG:  duration: 2722.541 ms  statement: with listing as (
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-2] #011            select l.*, array_remove(array[
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-3] #011                case (l.projection->'extensions'->0->'data'->'isPledge1Percent')::boolean  when true then 'pledge' end,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-4] #011                case (l.projection->'extensions'->0->'data'->'isDiverseOwnedBusiness')::boolean when true then 'diverse' end,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-5] #011                case (l.projection->'extensions'->0->'data'->'isAccessibleSolution')::boolean when true then 'accessible' end],null) as impact
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-6] #011                from projections l
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-7] #011                where l."type" ='listing'
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-8] #011                and (l.projection->'public')::boolean is true and ((l.projection->'extensions'->0->'data'->'isDiverseOwnedBusiness')::boolean is true
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-9] #011                or (l.projection->'extensions'->0->'data'->'isPledge1Percent')::boolean is true
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-10] #011                or (l.projection->'extensions'->0->'data'->'isAccessibleSolution')::boolean is true)
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-11] #011            )  select
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-12] #011            ranked.*
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-13] #011        from
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-14] #011            (
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-15] #011            select
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-16] #011                distinct(listing.external_id) as listing_projection_external_id,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-17] #011                unnest(listing.impact) as section_name,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-18] #011                listing.projection as listing_projection,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-19] #011                sd.projection->'popularity' as popularity,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-20] #011                rs.projection->'reviewCount' as review_count,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-21] #011                rs.projection->'averageRating' as average_rating,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-22] #011                c.projection->'active' as chat_active,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-23] #011                pp.projection->'price_model_type' as price_model_type,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-24] #011                pub.projection->'name' as publisher_name,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-25] #011                rank() over (partition by unnest(listing.impact)
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-26] #011            order by
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-27] #011                sd.projection->'popularity' desc)
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-28] #011            from
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-29] #011                 listing
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-30] #011            inner join (
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-31] #011                select
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-32] #011                    *
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-33] #011                from
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-34] #011                    projections
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-35] #011                where
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-36] #011                    type = 'search_details') sd on
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-37] #011                listing.external_id = sd.projection->>'listing'
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-38] #011            inner join (select * from projections where type = 'pricing_plan') pp on sd.projection->>'listing' = pp.projection->>'source_id'
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-39] #011            inner join (select * from streams where type = 'listing') s on s.id = listing.stream_id
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-40] #011            inner join projections pub on pub.stream_id = s.owner_id
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-41] #011            left join (
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-42] #011                select
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-43] #011                    *
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-44] #011                from
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-45] #011                    projections
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-46] #011                where
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-47] #011                    type = 'reviews_summary') rs on
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-48] #011                sd.projection->>'listing' = rs.projection->>'listing'
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-49] #011            left join (
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-50] #011                select
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-51] #011                    *
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-52] #011                from
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-53] #011                    projections
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-54] #011                where
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-55] #011                    type = 'chat_data') c on
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-56] #011                sd.projection->>'listing' = c.projection->>'listing'
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-57] #011        where listing.projection->'extensions'->0->'data'->>'marketplace' = 'English'
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-58] #011            and (listing.projection->'public')::boolean is true
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-59]
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-60] #011          ) ranked
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-61] #011        where
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-62] #011            rank <= 2
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-63] #011        order by
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-64] #011            section_name,
2025-12-08T09:04:14+00:00 host app[postgres.2473715] [12-65] #011            rank