2
\$\begingroup\$

This is probably the noobiest SQL question ever, and I'm almost positive there's a JOIN or something in PostgreSQL for this, but I can't find it so I'm using a naive method. Here's a bit about my schema:

phrases:

CREATE TABLE phrases
(
  phrase_id bigserial NOT NULL,
  language character varying(4) NOT NULL,
  text text NOT NULL,
  CONSTRAINT phrases_pk PRIMARY KEY (phrase_id)
)

translations:

CREATE TABLE translations
(
  source_id bigint NOT NULL,
  destination_id bigint NOT NULL,
  CONSTRAINT translations_pk PRIMARY KEY (source_id, destination_id)
)

I use the following query to get 100 phrases (for two language pairs). This requires limiting the results to the language pairs, then finding their translations in the translations table:

SELECT P1.phrase_id, P1.language, P1.text, P2.phrase_id, P2.language, P2.text
FROM phrases P1, phrases P2, translations T
WHERE (P1.language = 'eng' AND P2.language = 'fra'
    AND P1.phrase_id = T.source_id
    AND P2.phrase_id = T.destination_id)
LIMIT 100

For what it's worth, the query runs in 34 ms with ~7.5m rows in the translations table, and 1m rows in the phrases table. I'll end up doing full text searches on the phrases table though, so I want to make the translation lookup as efficient as possible (as the translations table will grow ~5x faster than phrases). Lookup in translations is also the most common operation in this database.

Are there any fancy methods of accomplishing this more elegantly/efficiently in PostgreSQL?

P.S. - There is no foreign key constraint in the translations table because I am still in the process of sanitizing the database and removing old records. Importing into Postgres was impossible with the FK due to entries missing from phrases.

\$\endgroup\$
2
\$\begingroup\$

I recommend using more vertical space when you write a query to make it easier to read. That said, this should work:

SELECT 
    P1.phrase_id, 
    P1.language, 
    P1.text, 
    P2.phrase_id, 
    P2.language, 
    P2.text
FROM    phrases P1
INNER JOIN translations T
    ON P1.phrase_id = T.source_id
INNER JOIN phrases P2 
    ON P2.phrase_id = T.destination_id
WHERE   P1.language = 'eng' 
AND     P2.language = 'fra'
LIMIT 100;
\$\endgroup\$
  • \$\begingroup\$ There's a syntax error after the second INNER JOIN (extra )). Also, oddly enough the EXPLAIN for my query and yours give the exact same output... It still ends up running a nested loop, but maybe that can't be avoided... +1 for the formatting suggestion though :) Do you have any other ideas? I'm not sure if this can even get more efficient now =/ \$\endgroup\$ – Chris Cirefice Nov 19 '14 at 14:46
  • \$\begingroup\$ Whoops, good catch. I'm not surprised the query plan is the same (as it is doing the same thing) but yours are ANSI-92 joins and those can be problematic. This article may be interesting to you on that topic. \$\endgroup\$ – Phrancis Nov 19 '14 at 16:38
  • \$\begingroup\$ Ah, good read! I'll change that right away :P \$\endgroup\$ – Chris Cirefice Nov 19 '14 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.