In my table plin_korisnik, I have field active which is defined as boolean type.
I'm trying to execute this query to fetch data from that table and two other tables:
SELECT
pk.omm AS omm,
pk.br_plin AS br_plin,
pk.naziv AS naziv,
pk.ulica||' '||pk.kbr AS adresa,
pk.pu||' - '||pk.naziv_pu AS mjesto,
po.datum AS datum,
CASE WHEN po.stanje >= 999999 THEN NULL ELSE po.stanje END AS stanje,
po.napomena AS napomena,
po.plin_postar AS laus,
pp.ime||' '||pp.prezime AS postar
FROM plin_korisnik pk
INNER JOIN
plin_ocitanje po ON pk.omm = po.omm
INNER JOIN plin_postar pp ON pp.laus = po.plin_postar
WHERE po.datum>='2017-01-26'
AND po.datum<='2017-01-26'
AND pk.tip='p'
AND pk.active = TRUE
ORDER BY po.datum, pk.naziv
but query takes to much time (like forever; I interrupted it after half an hour), but when I remove pk.active = TRUE test from WHERE clause, then query executes with expected speed. I had try to cast boolean type to integer, but problem remains.
I'd appreciate it if someone could explain how to use a boolean field in this and similar queries. active field is not indexed, maybe it should be, please help.
EDIT: After few hours of thinking I came out with this solution which use WITH clause:
WITH pk AS (
SELECT * FROM plin_korisnik WHERE active AND tip='p'
)
SELECT
pk.omm AS omm,
pk.br_plin AS br_plin,
pk.naziv AS naziv,
pk.ulica||' '||pk.kbr AS adresa,
pk.pu||' - '||pk.naziv_pu AS mjesto,
po.datum AS datum,
CASE WHEN po.stanje >= 999999 THEN NULL ELSE po.stanje END AS stanje,
po.napomena AS napomena,
po.plin_postar AS laus,
pp.ime||' '||pp.prezime AS postar
FROM pk
INNER JOIN
plin_ocitanje po ON pk.omm = po.omm
INNER JOIN plin_postar pp ON pp.laus = po.plin_postar
WHERE po.datum>='2017-01-26'
AND po.datum<='2017-01-26'
ORDER BY po.datum, pk.naziv;
active?explain (analyze, verbose)for the fast query and a simpleexplainfor the query that you cancelled. Formatted text please, no screen shots