On Jun 26, 2013, at 5:22 PM, Marcin Mańk wrote:
> On Wed, Jun 26, 2013 at 2:29 AM, Ben wrote:
>
>> shouldn't an index scan definitely be fastest here? you don't need to touch
>> the whole table or index. maybe there something i have misconfigured here?
>>
>
> How about you try increasing w
On Wed, Jun 26, 2013 at 2:29 AM, Ben wrote:
> shouldn't an index scan definitely be fastest here? you don't need to touch
> the whole table or index. maybe there something i have misconfigured here?
>
How about you try increasing work_mem ? I think a hash join may be the
best plan here, and i
On 06/25/2013 06:41 PM, Tom Lane wrote:
>> Well, it was more in the form of:
>> tab1.x = COALESCE(tab2.y,tab1.x)
>
> Hm. I'm not following how you get from there to complaining about not
> being smart about X = X, because that surely ain't the same.
Actually, it was dominated by defaultsel, sinc
1)
attrelid | attname | attstattarget
--+-+---
g2 | gid | 100
d2 | gid | 100
(2 rows)
setting statistics too 500 works!
I already tried overruling pg_statistic.stadistinct, but that didn't work.
thank you all for your help!!
C
On Wed, Jun 26, 2013 at 11:20 PM, Willy-Bas Loos wrote:
> On Wed, Jun 26, 2013 at 10:55 PM, Sergey Konoplev wrote:
>
>>
>>
>> These are plans of two different queries. Please show the second one
>> (where d2, g2, etc are) with secscans off.
>>
>>
> yes, you're right sry for the confusion.
> here'
On Wed, Jun 26, 2013 at 12:18 PM, Willy-Bas Loos wrote:
> plan with enable_seqscan off:
>
> Aggregate (cost=253892.48..253892.49 rows=1 width=0) (actual
> time=208.681..208.681 rows=1 loops=1)
>
The estimated cost of this is ~4x times greater than the estimated cost for
the sequential scan. I
On Wed, Jun 26, 2013 at 12:18 PM, Willy-Bas Loos wrote:
> plan with enable_seqscan off:
>
> Aggregate (cost=253892.48..253892.49 rows=1 width=0) (actual
> time=208.681..208.681 rows=1 loops=1)
> -> Nested Loop (cost=5.87..253889.49 rows=1198 width=0) (actual
> time=69.403..208.647 rows=17 loo
On Wed, Jun 26, 2013 at 10:36:10PM +0200, Willy-Bas Loos wrote:
> On Wed, Jun 26, 2013 at 10:31 PM, Jeff Janes wrote:
>
> >
> > Why is it retrieving 3.1 million, when it only needs 17?
> >
> >
> > that's because of the sequential scan, it reads all the data.
>
> cheers,
>
> willy-bas
Well, the
2013/6/26 Willy-Bas Loos
> postgres does a seqscan, even though there is an index present and it
> should be much more efficient to use it.
> I tried to synthetically reproduce it, but it won't make the same choice
> when i do.
> I can reproduce it with a simplified set of the data itself though.
On Wed, Jun 26, 2013 at 10:31 PM, Jeff Janes wrote:
>
> Why is it retrieving 3.1 million, when it only needs 17?
>
>
> that's because of the sequential scan, it reads all the data.
cheers,
willy-bas
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
On Wed, Jun 26, 2013 at 12:07 PM, Scott Marlowe wrote:
> On Wed, Jun 26, 2013 at 9:45 AM, Willy-Bas Loos
> wrote:
> >
> > Aggregate (cost=60836.71..60836.72 rows=1 width=0) (actual
> > time=481.526..481.526 rows=1 loops=1)
> > -> Hash Join (cost=1296.42..60833.75 rows=1184 width=0) (actual
>
On Wed, Jun 26, 2013 at 9:30 PM, Igor Neyman wrote:
>
> How much RAM you have on this machine?
>
16 GB
> What else is this machine is being used for (besides being db server)?
>
It's my laptop by now, but i was working on a server before that. The
laptop gives me some liberties to play around.
I
From: Willy-Bas Loos [mailto:willy...@gmail.com]
Sent: Wednesday, June 26, 2013 3:19 PM
To: Igor Neyman
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] seqscan for 100 out of 3M rows, index present
plan with enable_seqscan off:
Aggregate (cost=253892.48..253892.49 rows=1 width=0)
plan with enable_seqscan off:
Aggregate (cost=253892.48..253892.49 rows=1 width=0) (actual
time=208.681..208.681 rows=1 loops=1)
-> Nested Loop (cost=5.87..253889.49 rows=1198 width=0) (actual
time=69.403..208.647 rows=17 loops=1)
-> Index Scan using geo_blok_idx on geo g (cost=0.00
From: Willy-Bas Loos [mailto:willy...@gmail.com]
Sent: Wednesday, June 26, 2013 3:04 PM
To: Igor Neyman
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] seqscan for 100 out of 3M rows, index present
nope
$ grep ^[^#] /etc/postgresql/9.1/main/postgresql.conf|grep -e ^[^[:space:]]
data
On Wed, Jun 26, 2013 at 9:45 AM, Willy-Bas Loos wrote:
> Hi,
>
> postgres does a seqscan, even though there is an index present and it should
> be much more efficient to use it.
> I tried to synthetically reproduce it, but it won't make the same choice
> when i do.
> I can reproduce it with a simp
nope
$ grep ^[^#] /etc/postgresql/9.1/main/postgresql.conf|grep -e ^[^[:space:]]
data_directory = '/var/lib/postgresql/9.1/main'# use data in
another directory
hba_file = '/etc/postgresql/9.1/main/pg_hba.conf'# host-based
authentication file
ident_file = '/etc/postgresql/9.1/main/pg_ide
Hi,
postgres does a seqscan, even though there is an index present and it should be
much more efficient to use it.
I tried to synthetically reproduce it, but it won't make the same choice when i
do.
I can reproduce it with a simplified set of the data itself though.
here's the query, and the anal
Hi,
postgres does a seqscan, even though there is an index present and it
should be much more efficient to use it.
I tried to synthetically reproduce it, but it won't make the same choice
when i do.
I can reproduce it with a simplified set of the data itself though.
here's the query, and the anal
19 matches
Mail list logo
