Sergey Burladyan writes:
> Hot standby:
...
> ' -> Index Only Scan using items_user_id_idx on public.items
> (cost=0.00..24165743.48 rows=200673143 width=8) (actual
> time=56064.499..56064.499 rows=1 loops=1)'
> 'Output: public.items.user_id'
> 'Index
Alvaro Herrera writes:
> Tom Lane escribió:
>> It calls get_variable_range, which only looks at the pg_statistic entries.
> Uh? It's right there in line 2976 in HEAD.
Meh. You're right, I was thinking of this bit in get_variable_range()
/*
* XXX It's very tempting to try to use the a
Tom Lane escribió:
> Jeff Janes writes:
> > On Thu, Aug 1, 2013 at 5:16 PM, Sergey Burladyan
> > wrote:
> >> If I not mistaken, may be two code paths like this here:
> >> (1) mergejoinscansel -> scalarineqsel-> ineq_histogram_selectivity ->
> >> get_actual_variable_range -> index_getnext
> >> (
Jeff Janes writes:
> On Fri, Aug 2, 2013 at 2:58 AM, Sergey Burladyan wrote:
> >
> > PS: I think my main problem is here:
> > select min(user_id) from items;
> > min
> > -
> >1
> > (1 row)
> >
> > Time: 504.520 ms
>
> That is a long time, but still 100 fold less than the planner is taki
Tom Lane writes:
> Jeff Janes writes:
> > On Thu, Aug 1, 2013 at 5:16 PM, Sergey Burladyan
> > wrote:
> >> If I not mistaken, may be two code paths like this here:
> >> (1) mergejoinscansel -> scalarineqsel-> ineq_histogram_selectivity ->
> >> get_actual_variable_range -> index_getnext
> >> (
Jeff Janes writes:
> On Thu, Aug 1, 2013 at 5:16 PM, Sergey Burladyan wrote:
>> If I not mistaken, may be two code paths like this here:
>> (1) mergejoinscansel -> scalarineqsel-> ineq_histogram_selectivity ->
>> get_actual_variable_range -> index_getnext
>> (2) scalargtsel -> scalarineqsel -> i
On Thu, Aug 1, 2013 at 5:16 PM, Sergey Burladyan wrote:
> I also find this trace for other query:
> explain select * from xview.user_items_v v where ( v.item_id = 132358330 );
>
>
> If I not mistaken, may be two code paths like this here:
> (1) mergejoinscansel -> scalarineqsel-> ineq_histogram_se
On Fri, Aug 2, 2013 at 2:58 AM, Sergey Burladyan wrote:
>
> PS: I think my main problem is here:
> select min(user_id) from items;
> min
> -
>1
> (1 row)
>
> Time: 504.520 ms
That is a long time, but still 100 fold less than the planner is taking.
What about max(user_id)?
>
> also, i c
Sergey Burladyan escribió:
> I also find this trace for other query:
> explain select * from xview.user_items_v v where ( v.item_id = 132358330 );
>
> #0 0x7ff766967620 in read () from /lib/libc.so.6
> #1 0x7ff7689cfc25 in FileRead ()
> #2 0x7ff7689ea2f6 in mdread ()
> #3 0x7ff
I also find this trace for other query:
explain select * from xview.user_items_v v where ( v.item_id = 132358330 );
#0 0x7ff766967620 in read () from /lib/libc.so.6
#1 0x7ff7689cfc25 in FileRead ()
#2 0x7ff7689ea2f6 in mdread ()
#3 0x7ff7689cc473 in ?? ()
#4 0x7ff7689ccf54
Jeff Janes writes:
> I think the next step would be to run gdb -p (but don't start
> gdb until backend is in the middle of a slow explain), then:
Sorry, I am lack debug symbols, so call trace is incomplete:
explain select i.item_id, u.user_id from items i left join users u on u.user_id
= i.us
On Thu, Aug 1, 2013 at 12:13 PM, Sergey Burladyan wrote:
> Jeff Janes writes:
>
>> I'd use strace to find what file handle is being read and written, and
>> lsof to figure out what file that is.
>
> I use strace, it is more read then write:
> $ cut -d '(' -f 1 /var/tmp/pg.trace | sort | uniq -c |
Jeff Janes writes:
> I'd use strace to find what file handle is being read and written, and
> lsof to figure out what file that is.
I use strace, it is more read then write:
$ cut -d '(' -f 1 /var/tmp/pg.trace | sort | uniq -c | sort -n
49 select
708 close
1021 open
7356 write
21
On Thu, Aug 1, 2013 at 8:17 AM, Sergey Burladyan wrote:
> Sergey Burladyan writes:
>
>> # explain
>> # select i.item_id, u.user_id from items i
>> # left join users u on u.user_id = i.user_id
>> # where item_id = 169946840;
>> QUERY PLAN
>>
On Thu, Aug 01, 2013 at 07:17:27PM +0400, Sergey Burladyan wrote:
- Sergey Burladyan writes:
-
- > # explain
- > # select i.item_id, u.user_id from items i
- > # left join users u on u.user_id = i.user_id
- > # where item_id = 169946840;
- > QUERY PLAN
- >
--
Sergey Burladyan writes:
> # explain
> # select i.item_id, u.user_id from items i
> # left join users u on u.user_id = i.user_id
> # where item_id = 169946840;
> QUERY PLAN
> --
> Nested Loop Left Join (c
I find another query with big planning time:
explain select * from xview.user_items_v v where ( v.item_id = 132358330 );
QUERY PLAN
--
On Thu, Aug 1, 2013 at 2:04 PM, Thomas Reiss wrote:
> Le 01/08/2013 11:55, Sergey Burladyan a écrit :
> At first look, this reminds me some catalog bloat issue. Can you provide
> the result of these queries :
> SELECT pg_size_pretty(pg_table_size('pg_class')) AS size_pg_class;
> SELECT pg_size_pre
01.08.2013 14:05 пользователь "Thomas Reiss"
написал:
>
> If you leave enable_mergejoin to on, what happens if you run the explain
> two time in a row ? Do you get the same planning time ?
Yes, I get the same planning time.
Le 01/08/2013 11:55, Sergey Burladyan a écrit :
> Hello, i have a problem with planning time, I do not understand why this
> can happen.
>
> PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
> (Debian 4.4.5-8) 4.4.5, 64-bit
>
> # explain
> # select i.item_id, u.user_id from i
Hello, i have a problem with planning time, I do not understand why this can
happen.
PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 64-bit
# explain
# select i.item_id, u.user_id from items i
# left join users u on u.user_id = i.user_id
# where item
21 matches
Mail list logo
