Stefan,
See below
> -Original Message-
> From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-
> performance-ow...@postgresql.org] On Behalf Of Weinzierl Stefan
> Sent: Thursday, June 05, 2014 3:36 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Se
Hello,
I'm currently testing some queries on data which I had imported from an
other database-system into Postgres 9.4.
After the import I did create the indexes, run an analyze and vacuum. I
also played a little bit with seq_page_cost and random_page_cost. But
currently I have no clue, whic
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
as 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
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
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
Dmitry Karasik writes:
> I need to have a sort of a time machine, where select statements on tables
> could be easily replaced to select statements on tables as they were some
> time in the past,
> including all related table. To do so, I used views (see in the script) that
> UNION
> both curre
Dear all,
I have a problem with seqscan I hope you might help me with.
Attached is the simple script that reproduces a database and results, which I
have tested both on 9.0.4 and 9.3-devel with identical results.
I need to have a sort of a time machine, where select statements on tables
could be
On 8 June 2012 11:58, Albe Laurenz wrote:
> Did you take caching of table data in the buffer cache or the filesystem
> cache into account? Did you run your tests several times in a row and
> were the actual execution times consistent?
Yes, and yes.
>> Would tweaking enable_seqscan and other pl
Ivan Voras wrote:
> I have a SQL function (which I've pasted below) and while testing its
> code directly (outside a function), this is the "normal", default
plan:
>
> http://explain.depesz.com/s/vfP (67 ms)
>
> and this is the plain with enable_seqscan turned off:
>
> http://explain.depesz.com/
2012/5/27 Ivan Voras :
> On 27 May 2012 05:28, Pavel Stehule wrote:
>> Hello
>>
>> 2012/5/26 Ivan Voras :
>>> Hello,
>>>
>>> I have a SQL function (which I've pasted below) and while testing its
>>> code directly (outside a function), this is the "normal", default plan:
>>>
>>> http://explain.depe
On 27 May 2012 05:28, Pavel Stehule wrote:
> Hello
>
> 2012/5/26 Ivan Voras :
>> Hello,
>>
>> I have a SQL function (which I've pasted below) and while testing its
>> code directly (outside a function), this is the "normal", default plan:
>>
>> http://explain.depesz.com/s/vfP (67 ms)
>>
>> and thi
Hello
2012/5/26 Ivan Voras :
> Hello,
>
> I have a SQL function (which I've pasted below) and while testing its
> code directly (outside a function), this is the "normal", default plan:
>
> http://explain.depesz.com/s/vfP (67 ms)
>
> and this is the plain with enable_seqscan turned off:
>
> http:/
Hello,
I have a SQL function (which I've pasted below) and while testing its
code directly (outside a function), this is the "normal", default plan:
http://explain.depesz.com/s/vfP (67 ms)
and this is the plain with enable_seqscan turned off:
http://explain.depesz.com/s/EFP (27 ms)
Disabling s
2012/4/16 Tomek Walkuski
> Hello group!
>
> I have query like this:
>
> SELECT
> employments.candidate_id AS candidate_id,
> SUM(TS_RANK(employers.search_vector, TO_TSQUERY('simple', 'One:* |
> Two:* | Three:* | Four:*'), 2)) AS ts_rank
> FROM
> employments
> INNER JOIN
> employers ON employm
On Mon, Apr 16, 2012 at 9:02 AM, Tomek Walkuski
wrote:
> Hello group!
>
> I have query like this:
>
> SELECT
> employments.candidate_id AS candidate_id,
> SUM(TS_RANK(employers.search_vector, TO_TSQUERY('simple', 'One:* |
> Two:* | Three:* | Four:*'), 2)) AS ts_rank
> FROM
> employments
> INNER
On 16.4.2012 16:02, Tomek Walkuski wrote:
> Hello group!
>
> I have query like this:
>
> SELECT
> employments.candidate_id AS candidate_id,
> SUM(TS_RANK(employers.search_vector, TO_TSQUERY('simple', 'One:* |
> Two:* | Three:* | Four:*'), 2)) AS ts_rank
> FROM
> employments
> INNER JOIN
>
Hello group!
I have query like this:
SELECT
employments.candidate_id AS candidate_id,
SUM(TS_RANK(employers.search_vector, TO_TSQUERY('simple', 'One:* |
Two:* | Three:* | Four:*'), 2)) AS ts_rank
FROM
employments
INNER JOIN
employers ON employments.employer_id = employers.id
AND
employe
Tom Lane writes:
Vlad Arkhipov <[EMAIL PROTECTED]> writes:
I've just discovered a problem with quite simple query. It's really
confusing me.
Postgresql 8.3.1, random_page_cost=1.1. All tables were analyzed before
query.
What have you got effective_cache_size set to?
Vlad Arkhipov <[EMAIL PROTECTED]> writes:
> I've just discovered a problem with quite simple query. It's really
> confusing me.
> Postgresql 8.3.1, random_page_cost=1.1. All tables were analyzed before
> query.
What have you got effective_cache_size set to?
regards, tom
I've just discovered a problem with quite simple query. It's really
confusing me.
Postgresql 8.3.1, random_page_cost=1.1. All tables were analyzed before
query.
EXPLAIN ANALYZE
SELECT i.c, d.r
FROM i
JOIN d ON d.cr = i.c
WHERE i.dd between '2007-08-01' and '2007-08-30'
Hash Join (cost=2505.4
(Please don't top-post. )
Adrian Demaestri skrev:
> */Jeff Davis <[EMAIL PROTECTED]>/* escribió:
>
> On Mon, 2007-10-22 at 19:24 -0700, Adrian Demaestri wrote:
> > Hi,
> > I think planner should use other plans than seqscan to solve querys
> > like select * from hugetable limit 1,
It is not actualy a table, sorry, it is a quite complex view that involve three
large tables. When I query the view using a where clause the answer is fast
because of the use of some restrictive indexes, but when there is no where
clause the "limit 1" waits until the entire table is generated an
On Mon, 2007-10-22 at 19:24 -0700, Adrian Demaestri wrote:
> Hi,
> I think planner should use other plans than seqscan to solve querys
> like select * from hugetable limit 1, especially when the talbe is
> very large. Is it solved in newer versions or is there some open
> issues about it?.
> than
Hi,
I think planner should use other plans than seqscan to solve querys like select
* from hugetable limit 1, especially when the talbe is very large. Is it solved
in newer versions or is there some open issues about it?.
thanks
I'm working with postgres 8.0.1,
Tom Lane wrote:
One reason you might consider updating is that newer versions check the
physical table size instead of unconditionally believing
pg_class.relpages/reltuples. Thus, they're much less likely to get
fooled when a table has grown substantially since it was last vacuumed
or analyzed
Carlos Moreno <[EMAIL PROTECTED]> writes:
> But I think the problem is that this particular table had not been
> vacuum analyzed after having inserted the 2 records (the
> query planner was giving me seq. scan when the table had about
> a dozen records --- and seq. scan was, indeed, 10 times f
Tomas Vondra wrote:
When I force it via "set enable_seqscan to off", the index scan
takes about 0.1 msec (as reported by explain analyze), whereas
with the default, it chooses a seq. scan, for a total execution
time around 10 msec!! (yes: 100 times slower!). The table has
20 thousand record
On Sat, 2007-01-27 at 21:44 +1100, Russell Smith wrote:
> Guido Neitzer wrote:
> > On 27.01.2007, at 00:35, Russell Smith wrote:
> >
> >> Guess 1 would be that your primary key is int8, but can't be certain
> >> that is what's causing the problem.
> >
> > Why could that be a problem?
> Before 8.0,
Guido Neitzer wrote:
On 27.01.2007, at 00:35, Russell Smith wrote:
Guess 1 would be that your primary key is int8, but can't be certain
that is what's causing the problem.
Why could that be a problem?
Before 8.0, the planner would not choose an index scan if the types were
different int8_col
>
> Hi,
>
> I find various references in the list to this issue of queries
> being too slow because the planner miscalculates things and
> decides to go for a sequenctial scan when an index is available
> and would lead to better performance.
>
> Is this still an issue with the latest version?
On 27.01.2007, at 00:35, Russell Smith wrote:
Guess 1 would be that your primary key is int8, but can't be
certain that is what's causing the problem.
Why could that be a problem?
cug
---(end of broadcast)---
TIP 3: Have you checked our exten
Carlos Moreno wrote:
Hi,
I find various references in the list to this issue of queries
being too slow because the planner miscalculates things and
decides to go for a sequenctial scan when an index is available
and would lead to better performance.
Is this still an issue with the latest versi
Carlos Moreno skrev:
When I force it via "set enable_seqscan to off", the index scan
takes about 0.1 msec (as reported by explain analyze), whereas
>
For the time being, I'm using an explicit "enable_seqscan off"
in the client code, before executing the select. But I wonder:
Is this still a
Hi,
I find various references in the list to this issue of queries
being too slow because the planner miscalculates things and
decides to go for a sequenctial scan when an index is available
and would lead to better performance.
Is this still an issue with the latest version? I'm doing some
t
On Fri, 17 Dec 2004 23:37:37 -0500
Tom Lane <[EMAIL PROTECTED]> wrote:
> Frank Wiles <[EMAIL PROTECTED]> writes:
> > I've also seen a huge difference between select count(*) and
> > select count(1) in older versions,
>
> That must have been before my time, ie, pre-6.4 or so. There is
> cert
On Fri, Dec 17, 2004 at 10:39:18PM -0600, Bruno Wolff III wrote:
> It doesn't seem totally out of wack. You will be limited by the memory
> bandwidth and it looks like you get something on the order of a few
> hundred references to memory per row. That may be a little high, but
> it doesn't seem ri
Frank Wiles <[EMAIL PROTECTED]> writes:
> I've also seen a huge difference between select count(*) and
> select count(1) in older versions,
That must have been before my time, ie, pre-6.4 or so. There is
certainly zero difference now.
regards, tom lane
-
On Fri, Dec 17, 2004 at 22:56:27 +0100,
"Steinar H. Gunderson" <[EMAIL PROTECTED]> wrote:
>
> I'm a bit unsure -- should counting ~3 million rows (no OIDs, PG 7.4,
> everything in cache, 32-byte rows) take ~3500ms on an Athlon 64 2800+?
It doesn't seem totally out of wack. You will be limited b
On Fri, Dec 17, 2004 at 05:02:29PM -0600, Frank Wiles wrote:
> It depends more on your disk IO than the processor. Counting isn't
> processor intensive, but reading through the entire table on disk
> is. I've also seen a huge difference between select count(*) and
> select count(1) in o
On Fri, 17 Dec 2004 23:09:07 +0100
"Steinar H. Gunderson" <[EMAIL PROTECTED]> wrote:
> On Fri, Dec 17, 2004 at 10:56:27PM +0100, Steinar H. Gunderson wrote:
> > I'm a bit unsure -- should counting ~3 million rows (no OIDs, PG
> > 7.4, everything in cache, 32-byte rows) take ~3500ms on an Athlon 64
On Fri, Dec 17, 2004 at 10:56:27PM +0100, Steinar H. Gunderson wrote:
> I'm a bit unsure -- should counting ~3 million rows (no OIDs, PG 7.4,
> everything in cache, 32-byte rows) take ~3500ms on an Athlon 64 2800+?
(I realize I was a bit unclear here. This is a completely separate case, not
relate
On Fri, Dec 17, 2004 at 10:47:57AM -0500, Greg Stark wrote:
>> Must admit this puzzles me. Are you saying you can't saturate your disk I/O?
>> Or
>> are you saying other DBMS store records in 0.5 to 0.2 times less space than
>> PG?
> I don't know what he's talking about either. Perhaps he's think
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> I think the one effect that's not being modeled is amortization of index
>> fetches across successive queries.
> And across multiple fetches in a single query, such as with a nested loop.
Right, that's effectively
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Postgres is also more pessimistic about the efficiency of index scans. It's
> > willing to use a sequential scan down to well below 5% selectivity when
> > other
> > databases use the more traditional rule of thumb
Greg Stark <[EMAIL PROTECTED]> writes:
> Postgres is also more pessimistic about the efficiency of index scans. It's
> willing to use a sequential scan down to well below 5% selectivity when other
> databases use the more traditional rule of thumb of 10%.
However, other databases are probably basi
Richard Huxton <[EMAIL PROTECTED]> writes:
> Not going to do anything in this case. The planner is roughly right about how
> many rows will be returned, it's just not expecting everything to be in RAM.
That doesn't make sense or else it would switch to the index at
random_page_cost = 1.0. If it w
David Brown wrote:
You might want to reduce random_page_cost a little.
Keep in mind that your test case is small enough to fit in RAM and
is probably not reflective of what will happen with larger tables.
I am also running 8.0 rc1 for Windows. Despite many hours spent
tweaking various planner cos
> You might want to reduce random_page_cost a little.
> Keep in mind that your test case is small enough to fit in RAM and is
> probably not reflective of what will happen with larger tables.
I am also running 8.0 rc1 for Windows. Despite many hours spent tweaking
various planner cost constants,
Jon Anderson <[EMAIL PROTECTED]> writes:
> Any hints on what to do to make PostgreSQL use the index?
You might want to reduce random_page_cost a little.
Keep in mind that your test case is small enough to fit in RAM and is
probably not reflective of what will happen with larger tables.
I have a table 'Alias' with 541162 rows. It's created as follows:
CREATE TABLE alias
(
id int4 NOT NULL,
person_id int4 NOT NULL,
last_name varchar(30),
first_name varchar(30),
middle_name varchar(30),
questioned_identity_flag varchar,
CONSTRAINT alias_pkey PRIMARY KEY (id)
)
Afte
Jim C. Nasby wrote:
> I'm wondering if there's any way I can tweak things so that the estimate
> for the query is more accurate (I have run analyze):
Can you post your configuration file ? I'd like to see for example your
settings about: random_page_cost and effective_cache_size.
Regards
Gaetano M
Which column would you recommend? Did something stick out at you?
On Tue, Nov 09, 2004 at 03:14:36PM -0800, Joshua D. Drake wrote:
>
> >opensims=#
> >
> >I'd really like to avoid putting a 'set enable_seqscan=false' in my
> >code, especially since this query only has a problem if it's run on a
>
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I'm wondering if there's any way I can tweak things so that the estimate
> for the query is more accurate (I have run analyze):
>-> Index Scan using alert__tick_tsz on alert
> (cost=0.00..2498.49 rows=7119 width=28) (actual time=0.006
opensims=#
I'd really like to avoid putting a 'set enable_seqscan=false' in my
code, especially since this query only has a problem if it's run on a
large date/time window, which normally doesn't happen.
Try increasing your statistics target for the column and then rerunning
analyze.
Sincerely
I'm wondering if there's any way I can tweak things so that the estimate
for the query is more accurate (I have run analyze):
QUERY PLAN
> On Mon, 30 Aug 2004, Martin Sarsale wrote:
> > "Multicolumn indexes can only be used if the clauses involving the
> > indexed columns are joined with AND. For instance,
> >
> > SELECT name FROM test2 WHERE major = constant OR minor = constant;
>
> You can use DeMorgan's Theorem to transform an O
On Mon, 30 Aug 2004, Martin Sarsale wrote:
> "Multicolumn indexes can only be used if the clauses involving the
> indexed columns are joined with AND. For instance,
>
> SELECT name FROM test2 WHERE major = constant OR minor = constant;
You can use DeMorgan's Theorem to transform an OR clause to an
> Using a functional index you can define an index around the way you
> access the data. There is no faster or better way to do it...this is a
> mathematical truth, not a problem with the planner. Why not use the
> right tool for the job? A boolean index is super-efficient both in disk
> space
Martin Sarsale <[EMAIL PROTECTED]> writes:
> I indexed columns c and d (separately) but this query used the slow
> seqscan instead of the index scan:
> select * from t where c<>0 or d<>0;
> After playing some time, I noticed that if I change the "or" for an
> "and", pg used the fast index scan (b
Another option here is to use a partial index. You can index on some other
column -- perhaps the column you want the results ordered by where the where
clause is true.
Something like:
create index t_idx on t (name) where c>0 and d>0;
then any select with a matching where clause can use the inde
On Mon, 30 Aug 2004, Martin Sarsale wrote:
> On Mon, 2004-08-30 at 15:06, Merlin Moncure wrote:
> > create function is_somethingable (ctype, dtype) returns boolean as
>
> Thanks, but I would prefer a simpler solution.
>
> I would like to know why this uses a seqscan instead of an index scan:
>
> c
create index t_idx on t((c+d));
select * from t where c+d > 0;
Why not :
select ((select * from t where c<>0::bigint) UNION (select * from t where
d<>0::bigint))
group by whatever;
or someting ?
---(end of broadcast)---
TIP 3: if pos
On Mon, 2003-10-06 at 05:15, Andrew Sullivan wrote:
> There's plenty of academic work which purports to show that LRU is
> far from the best choice. Just in principle, it seems obvious that a
> single-case seqscan-type operation (such as vacuum does) is a good
> way to lose your cache for no real
76 matches
Mail list logo
