PgHero

Queries

Showing queries for postgres user. Show all

Total Time Average Time Calls
16 min < 0.1% 65 ms 14,675 postgres 
SELECT DISTINCT sub.record_id, $1 as rank
FROM (
  SELECT profile_id as record_id
  FROM email_addresses
  WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.term($2, $3), paradedb.term($4, $5), paradedb.term($6, $7)])
    
    AND deleted_at IS NULL
    AND profile_id IS NOT NULL
    
    
  LIMIT $8
) sub
15 min < 0.1% 222 ms 4,043 postgres 
SELECT profile_id as record_id,
       ROW_NUMBER() OVER (ORDER BY best_score DESC, profile_id) as rank
FROM (
  SELECT profile_id, MAX(score * (CASE WHEN end_date IS NULL THEN $1 ELSE GREATEST($2, $3 - EXTRACT($4 FROM AGE(NOW(), end_date)) / $5) END * CASE WHEN start_date IS NOT NULL THEN LEAST($6, $7 + EXTRACT($8 FROM AGE(COALESCE(end_date, NOW()), start_date)) / $9) ELSE $10 END * CASE WHEN source = $11 THEN $12 ELSE $13 END)) as best_score
  FROM (
    SELECT *
    FROM (
      SELECT profile_id, pdb.score(id) as score, end_date, start_date, source
      FROM profiles_positions
      WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.parse($14), paradedb.term($15, $16)])
        
        AND deleted_at IS NULL
        AND profile_id IS NOT NULL
      OFFSET $17
    ) matches
    WHERE $18 
    ORDER BY score DESC
    LIMIT $19
  ) topk
  GROUP BY profile_id
) sub
ORDER BY rank
LIMIT $20
13 min < 0.1% 25 ms 29,891 postgres 
SELECT DISTINCT sub.record_id, $1 as rank
FROM (
  SELECT id as record_id
  FROM profiles
  WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.term($2, $3), paradedb.term($4, $5), paradedb.term($6, $7)])
    
    AND deleted_at IS NULL
    
    
    
  LIMIT $8
) sub
12 min < 0.1% 298 ms 2,407 postgres 
WITH
all_matched_count AS (
  SELECT count(*) as cnt
  FROM profiles p
  
  
  
  WHERE p.deleted_at IS NULL
  AND p.group_id = $1::uuid
  
  AND EXISTS (SELECT $2 FROM profiles_associations pa WHERE pa.profile_id = p.id AND pa.group_id = $3::uuid AND pa.deleted_at IS NULL AND (pa.company_id = $4::uuid OR pa.project_id IN (SELECT id FROM projects WHERE company_id = $5::uuid AND deleted_at IS NULL)) AND pa.association_type = $6)
  
),
all_matched AS (
  SELECT p.id as id
  FROM profiles p
  
  
  
  WHERE p.deleted_at IS NULL
  AND p.group_id = $7::uuid
  
  AND EXISTS (SELECT $8 FROM profiles_associations pa WHERE pa.profile_id = p.id AND pa.group_id = $9::uuid AND pa.deleted_at IS NULL AND (pa.company_id = $10::uuid OR pa.project_id IN (SELECT id FROM projects WHERE company_id = $11::uuid AND deleted_at IS NULL)) AND pa.association_type = $12)
  
  ORDER BY p.id DESC NULLS LAST, p.id ASC
  LIMIT $13 OFFSET $14
)
SELECT json_build_object(
  $15, (SELECT COALESCE(json_agg(id), $16::json) FROM all_matched),
  $17, (SELECT cnt FROM all_matched_count),
  $18, $19::json
)::text as result
11 min < 0.1% 131 ms 4,855 postgres 
SELECT DISTINCT sub.record_id, $1 as rank
FROM (
  SELECT profile_id as record_id
  FROM profiles_positions
  WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.parse($2), paradedb.term($3, $4)])
    
    AND deleted_at IS NULL
    AND profile_id IS NOT NULL
    
    
  LIMIT $5
) sub
10 min < 0.1% 30 ms 19,989 postgres 
SELECT DISTINCT sub.record_id, $1 as rank
FROM (
  SELECT id as record_id
  FROM profiles
  WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.term($2, $3), paradedb.term($4, $5)])
    
    AND deleted_at IS NULL
    
    
    
  LIMIT $6
) sub
9 min < 0.1% 49 ms 11,485 postgres 
CREATE TEMP TABLE _all_matched ON COMMIT DROP AS SELECT p.id as id,
         ROW_NUMBER() OVER (ORDER BY p.trigger_last_interaction_date DESC NULLS LAST, p.id ASC) as rn
  FROM profiles p
  
  
  
  WHERE p.deleted_at IS NULL
  AND p.group_id = $1::uuid
  
  AND EXISTS (SELECT $2 FROM profiles_statuses_cache psc WHERE psc.profile_id = p.id AND psc.group_id = $3::uuid AND psc.is_talent = $4)
9 min < 0.1% 55 ms 10,088 postgres 
SELECT DISTINCT sub.record_id, $1 as rank
FROM (
  SELECT id as record_id
  FROM profiles
  WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.parse($2), paradedb.term($3, $4)])
    
    AND deleted_at IS NULL
    
    
    
  LIMIT $5
) sub
8 min < 0.1% 127 ms 3,972 postgres 
SELECT id as record_id,
       ROW_NUMBER() OVER (ORDER BY best_score DESC, id) as rank
FROM (
  SELECT id, MAX(score) as best_score
  FROM (
    SELECT id, pdb.score(id) as score
    FROM profiles
    WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.parse($1), paradedb.term($2, $3)])
      
      AND deleted_at IS NULL
      
    OFFSET $4
  ) matches
  GROUP BY id
) matched
WHERE $5 
ORDER BY rank
LIMIT $6
8 min < 0.1% 124 ms 3,966 postgres 
SELECT profile_id as record_id,
       ROW_NUMBER() OVER (ORDER BY best_score DESC, profile_id) as rank
FROM (
  SELECT profile_id, MAX(score) as best_score
  FROM (
    SELECT profile_id, pdb.score(id) as score
    FROM profiles_educations
    WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.parse($1), paradedb.term($2, $3)])
      
      AND deleted_at IS NULL
      AND profile_id IS NOT NULL
    OFFSET $4
  ) matches
  GROUP BY profile_id
) matched
WHERE $5 
ORDER BY rank
LIMIT $6
8 min < 0.1% 67 ms 6,818 postgres 
SELECT DISTINCT sub.record_id, $1 as rank
FROM (
  SELECT profile_id as record_id
  FROM email_addresses
  WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.term($2, $3), paradedb.term($4, $5)])
    
    AND deleted_at IS NULL
    AND profile_id IS NOT NULL
    
    
  LIMIT $6
) sub
6 min < 0.1% 98 ms 3,966 postgres 
SELECT profile_id as record_id,
       ROW_NUMBER() OVER (ORDER BY best_score DESC, profile_id) as rank
FROM (
  SELECT profile_id, MAX(score) as best_score
  FROM (
    SELECT profile_id, pdb.score(id) as score
    FROM profiles_skills
    WHERE id @@@ paradedb.boolean(must => ARRAY[paradedb.parse($1), paradedb.term($2, $3)])
      
      AND deleted_at IS NULL
      AND profile_id IS NOT NULL
    OFFSET $4
  ) matches
  GROUP BY profile_id
) matched
WHERE $5 
ORDER BY rank
LIMIT $6