2

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;
3
  • Why don't you add an index to active? Commented Jan 27, 2017 at 8:30
  • 4
    Please Edit your question and add the execution plan generated using explain (analyze, verbose) for the fast query and a simple explain for the query that you cancelled. Formatted text please, no screen shots Commented Jan 27, 2017 at 8:31
  • Than you @TimBiegeleisen. I created index on column "active", and query now finish after 13sec, which is somewhat acceptable. Most references on the Internet are saying that there is no point in making index on boolean column. Table is not so big 250.000+ rows from which 12.000+ rows are "active". Commented Jan 27, 2017 at 10:04

1 Answer 1

1

I think you may make it faster if you change the order in the where clause, start with the initial table. An index on pk.active and on po.datum will absolutely help. You may want to consider putting the po.datum in the inner join, instead of the where.

po.datum>='2017-01-26'
AND po.datum<='2017-01-26'
AND pk.tip='p'
AND pk.active = TRUE

Will be:

pk.active = TRUE
AND pk.tip='p'
AND po.datum>='2017-01-26'
AND po.datum<='2017-01-26'
Sign up to request clarification or add additional context in comments.

7 Comments

How would the order make a difference?
The order in the where can/should impact the order the query is executed. If the database can ignore 238000 of the 250000 rows (see comment above) after the first where it will be faster. checking a boolean in the left table is faster than checking 2 date fields in the right table
@Bojan please try this and tell us what happened
@verhie Sorry but it sounds like wishful thinking. I mean, changing the order in the where clause would change the planner behavior. You need to back that up with a sample of a query generating different plans for different where condition orders.
@Bojan Have you tried this and did it have any effect on the speed?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.