Re: Taking lot time - Mailing list pgsql-general

On 11/25/2015 07:46 AM, Ramesh T wrote:
>
> Hi All,
>          9.3 version using pgadmin3
>
> Query like below,20 columns fetching
>   SELECT col1,col2,col3,...col.20
>                                     FROM detail i,
>                                      adjdetail ia,
>                                    WHERE i.trans_id = ia.detail_id AND
> (i.event = ANY (ARRAY[21, 22, 3, 5]))
> created indexes on  where clause declared columns.
>
> Taking a lot of time above ..?any help apprectiated !.

Realized my previous post was not really helpful. What I should have
said is below.

To get a useful answer you will need to supply a complete(from one run
of the query) set of information for the problem you are experiencing.

So:

1) The complete definitions for the tables involved in the query,
including FKs, triggers, etc.

2) The query you are running.

3) The EXPLAIN ANALYZE for said query

4) The number of rows returned from the query.

5) The procedure you used to getting the time value and where it was
applied(psql, pgAdmin, etc).

>
> I have total 4 gb ram,i changed below in postgres.conf
>
> shared_buffers--1024mb
> temp_bufffers=8mb
> work_mem=200mb
> maintanace_work_mem=500mb
> seq_page_cost = 1.0
> random_page_cost = 5.0
> effective_cache_size = 1024MB
>
>
>
> On Wed, Nov 18, 2015 at 11:12 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 11/17/2015 04:18 AM, Ramesh T wrote:
>
>         the query is big it's selecting 20 rows from two table like i
>         mentioned
>         above exaplain analyze
>
>         what should i do..?any help
>
>
>     Please do not top post.
>
>     I must be missing a post, as I see no explanation of what the query
>     is doing.
>
>
>         On Wed, Nov 4, 2015 at 4:27 AM, Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>> wrote:
>
>              On 11/03/2015 06:42 AM, Ramesh T wrote:
>
>                  I have a Query it taking a lot of time to fetch results
>                  so,explain query gave
>
>                  "Hash Join  (cost=55078.00..202405.95 rows=728275
>         width=418)"
>                  "  Hash Cond: (itd.tran_id = iad._adj__id)"
>                  "  ->  Seq Scan on inv_detail itd  (cost=0.00..40784.18
>         rows=731029
>                  width=95)"
>                  "        Filter: (event_type = ANY
>         ('{21,22,3,5}'::integer[]))"
>                  "  ->  Hash  (cost=20590.78..20590.78 rows=610978
>         width=331)"
>                  "        ->  Seq Scan on inv_adj  iad  (cost=0.00..20590.78
>                  rows=610978
>                  width=331)"
>
>                  Can you Please let me know wt happen in query..?wt
>         should i do..
>
>
>              And the query is?
>
>
>              --
>              Adrian Klaver
>         adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com