From: Pallav Kalva <pkalva@deg.cc>
To: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor Query
Date: Mon, 06 Dec 2004 13:00:27 -0500
Message-ID: <41B49E3B.7030407@deg.cc> (raw)
In-Reply-To: <opsilkfkrucq72hf@musicbox>
References: <41B47A97.1090708@deg.cc>
<opsilfifducq72hf@musicbox>
<41B48053.7090805@deg.cc>
<opsilgs8b4cq72hf@musicbox>
<41B48ADB.7000100@deg.cc>
<opsilkfkrucq72hf@musicbox>
Pierre-Frédéric Caillaud wrote:
>
>
>> Just One, user can i have only one bankaccount.
>
>
> Ah well, in that case :
> This is your query :
>
> select userID, fname, lname, email, phone, dateEntered, dateCanceled,
> dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as
> searches
> from Users u
> where 1=1 AND exists (select userID
> from bankaccount ba
> where ba.bankaccountID = u.bankaccountID
> and ba.accountnumber = '12345678')
> AND exists (select userID
> from bankaccount ba
> where ba.bankaccountID = u.bankaccountID
> and ba.routingNumber = '12345678')
> order by UserID desc
> limit 500
>
> What it does is scan all users, and for each user, test if it has
> the accountnumber or the routingNumber you seek. You're reversing the
> problem : you should first look for accountnumber and routingNumber,
> THEN look for the user :
>
>
> SELECT * FROM Users WHERE bankaccountID IN
> (SELECT bankaccountID FROM bankaccount WHERE accountnumber =
> '12345678' OR/AND routingNumber = '12345678')
>
> or :
>
> SELECT * FROM Users WHERE userID IN
> (SELECT userID FROM bankaccount WHERE accountnumber = '12345678'
> OR/AND routingNumber = '12345678')
>
> There is something very strange in your query, it seems that
> bankaccount and Users both have a UserID column and a bankaccountID
> column. Is this normal ? It looks denormalized to me...
>
Userid column is only in users table not in bankaccounts table , based
on your suggestion i made changes to the query and here are the explain
plans :
select userID, fname, lname, email, phone, dateEntered, dateCanceled,
dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as searches
from Users u
where bankaccountid in (select bankaccountid
from bankaccount ba
where ba.bankaccountID = u.bankaccountID
and ba.accountnumber = '12345678')
AND bankaccountid in (select bankaccountid
from bankaccount ba
where ba.bankaccountID = u.bankaccountID
and ba.routingNumber = '12345678')
order by UserID desc
limit 500
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6642.59 rows=500 width=121) (actual
time=40180.116..93650.837 rows=1 loops=1)
-> Index Scan Backward using users_pkey on users u
(cost=0.00..1087936.69 rows=81891 width=121) (actual
time=40180.112..93650.829 rows=1 loops=1)
Filter: ((subplan) AND (subplan))
SubPlan
-> Index Scan using bankaccount_pkey on bankaccount ba
(cost=0.00..3.08 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=3)
Index Cond: (bankaccountid = $0)
Filter: (routingnumber = '12345678'::text)
-> Index Scan using bankaccount_pkey on bankaccount ba
(cost=0.00..3.08 rows=1 width=4) (actual time=0.004..0.004 rows=0
loops=385914)
Index Cond: (bankaccountid = $0)
Filter: (accountnumber = '12345678'::text)
Total runtime: 93684.307 ms
select userID, fname, lname, email, phone, dateEntered, dateCanceled,
dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as searches
from Users u
where bankaccountid in (select bankaccountid
from bankaccount ba
where ba.bankaccountID = u.bankaccountID
and ba.accountnumber = '12345678'
and ba.routingNumber = '12345678')
order by UserID desc
limit 500
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1777.53 rows=500 width=121) (actual
time=18479.669..63584.437 rows=1 loops=1)
-> Index Scan Backward using users_pkey on users u
(cost=0.00..582250.93 rows=163781 width=121) (actual
time=18479.663..63584.428 rows=1 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using bankaccount_pkey on bankaccount ba
(cost=0.00..3.09 rows=1 width=4) (actual time=0.004..0.004 rows=0
loops=385914)
Index Cond: (bankaccountid = $0)
Filter: ((accountnumber = '12345678'::text) AND
(routingnumber = '12345678'::text))
Total runtime: 63596.222 ms
What's wierd is even though there is a index on bankaccountid table it
doesnt use that index, it uses the index on the userid table and the
execution time is little better but it still takes over a minute to
execute .
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pkalva@deg.cc, lists@boutiquenumerique.com
Subject: Re: Poor Query
In-Reply-To: <41B49E3B.7030407@deg.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox