Re: [PERFORM] Simple join doesn't use index

On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski wrote: > > On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik wrote: > >> It sure turned out that default settings are not a good fit. >> > > do you know pgtune? > it's a good tool for starters, if you want a fast postgres and don't > really want to

Re: [PERFORM] Simple join doesn't use index

Setting work_mem to 64MB triggers in memory sort but look what happens with views look up. PG goes through all records there "Seq Scan on views" instead of using visitor_id index and I have only subset of real data to play around. Can imagine what cost would be running it against bigger dataset. So

Re: [PERFORM] Simple join doesn't use index

On Tue, Jan 29, 2013 at 8:24 AM, Alex Vinnik wrote: > On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski > wrote: > >> >> do you know pgtune? >> it's a good tool for starters, if you want a fast postgres and don't >> really want to learn what's behind the scenes. >> > Yeah.. I came across pgtu

Re: [PERFORM] Simple join doesn't use index

On Tue, Jan 29, 2013 at 8:41 AM, Alex Vinnik wrote: > Setting work_mem to 64MB triggers in memory sort but look what happens with > views look up. PG goes through all records there "Seq Scan on views" instead > of using visitor_id index and I have only subset of real data to play > around. Can ima

Re: [PERFORM] Simple join doesn't use index

On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote: > random_page_cost=1 might be not what you really want. > it would mean that random reads are as fast as as sequential reads, which > probably is true only for SSD > What randon_page_cost would be more appropriate for EC2 EBS Provisioned > volume

Re: [PERFORM] Simple join doesn't use index

On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot wrote: > On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote: > > random_page_cost=1 might be not what you really want. >> it would mean that random reads are as fast as as sequential reads, which >> probably is true only for SSD >> > What randon_page_cost

Re: [PERFORM] Simple join doesn't use index

On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik wrote: > > > > On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot wrote: >> >> On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote: >> >>> random_page_cost=1 might be not what you really want. >>> it would mean that random reads are as fast as as sequential read

Re: [PERFORM] Simple join doesn't use index

On Mon, Jan 28, 2013 at 3:43 PM, Alex Vinnik wrote: > It sure turned out that default settings are not a good fit. Setting > random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly see > that indexes are being used in explain plan and IO utilization is close to > 0. > > QUERY PLAN >

Re: [PERFORM] Simple join doesn't use index

On Tue, Jan 29, 2013 at 2:06 PM, Jeff Janes wrote: > > Sort Key: visits.id, views.id > > Sort Method: external sort Disk: 4248kB > > What query are you running? The query you originally showed us should > not be doing this sort in the first place. > > Cheers, > > Jeff > Here is the query

Re: [PERFORM] Simple join doesn't use index

index definition CREATE INDEX views_visit_id_visit_buoy_index ON views USING btree (visit_id, visit_buoy) On Tue, Jan 29, 2013 at 1:35 PM, Merlin Moncure wrote: > On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik > wrote: > > > > > > > > On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot > wrote: > >>

Re: [PERFORM] Simple join doesn't use index

On Mon, Jan 28, 2013 at 4:55 PM, Filip Rembiałkowski wrote: > > On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik wrote: >> >> It sure turned out that default settings are not a good fit. > > > do you know pgtune? > it's a good tool for starters, if you want a fast postgres and don't really > want to