Slow return of results from the database in PostgreSQL

I have table in DB with addresses (PostgreSQL):

CREATE TABLE addresses (
id serial4 NOT NULL,
prg_id text NULL,
text code NULL,
city text NULL,
street text NULL,
text number NULL,
district text NULL,
text address NULL
);
CREATE UNIQUE INDEX addresses_id_pkey ON address g USING btree (id);

The base has a couple of GB in total.

id = record id in the database, code = zip code, street = street, number = house number, county = county, address = zip code city street number

I have a problem with queries like:

- SELECT street, postcode, city, number, similarity('street' , '$address') AS similarity, row_number() OVER() AS rank FROM addresses where street LIKE '%$address%' and city = '$place' and code = '$code' order by rank LIMIT 1;
- SELECT street, postcode, city, street <-> '$address' AS distance, row_number() OVER () AS rank FROM addresses WHERE city = '$place' and code = '$code' ORDER BY distance LIMIT 1;
(returning the result takes up to 15 seconds).

the similarity function looks like this:

  RETURNS real
  LANGUAGE c
  IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$similarity$function$
;

Does anyone know how to optimize the query or database / table?

Please help me, i am beginner

LaryAI

3 years ago

Level 58

There are a few things that can be done to optimize the query and database/table:

Indexing: Make sure that the columns used in the WHERE clause and ORDER BY clause are indexed. In this case, the "street", "city", and "code" columns should be indexed.
Use EXPLAIN: Use the EXPLAIN command to analyze the query and see if there are any performance issues. This will help identify any slow parts of the query and suggest ways to optimize it.
Use a Full-Text Search: Instead of using the LIKE operator, use a full-text search. PostgreSQL has a built-in full-text search engine called tsvector/tsquery. This can be used to search for words or phrases in a more efficient way.
Use Prepared Statements: Use prepared statements to avoid recompiling the query each time it is executed. This can improve performance by reducing the overhead of query compilation.

Here is an example of how to use a prepared statement in PHP:

$stmt = $pdo->prepare('SELECT street, postcode, city, number, similarity(:address, street) AS similarity, row_number() OVER() AS rank FROM addresses WHERE street LIKE :address AND city = :place AND code = :code ORDER BY rank LIMIT 1');
$stmt->execute(['address' => "%$address%", 'place' => $place, 'code' => $code]);

Note that the variables are passed as parameters to the prepared statement, which helps prevent SQL injection attacks.

Please or to participate in this conversation.