Re: [BUGS] BUG #4819: Ordering big tables by indexed columns is very slow.
On May 21, 3:04 am, alexei_nova...@yahoo.com ("Alexei") wrote:
> The following bug has been logged online:
>
> Bug reference: 4819
> Logged by: Alexei
> Email address: alexei_nova...@yahoo.com
> PostgreSQL version: 8.3.7
> Operating system: Open SuSE 11.1 AMD Athlon 64 X2
> Description: Ordering big tables by indexed columns is very slow.
> Details:
>
> Hello.
>
> I have very simple query, which runs very long when has "order by" clause,
> even though all columns in "order by" are indexed. Here is the simplified
> testcase.
>
> 1) Table:
> create table tmp1
> (
> field1 bigint not null,
> field2 integer not null
> )
>
> 2) Data:
> I generated some test data for this table: field2 is always 2; field1 starts
> from 1242865824484 and every next one is incremented by 1. I generated 3
> million records.
>
> 3) Index:
> create index tmp1_idx on tmp1 (field1, field2)
>
> 4) Query:
> select field1, field2 from tmp1 order by 1, 2
>
> The query plan for this query is:
> Sort (cost=522779.47..530279.47 rows=300 width=12)
> Sort Key: field1, field2
> -> Seq Scan on tmp1 (cost=0.00..46217.00 rows=300 width=12)
>
> Index is not used for the sorting here. But if I add "limit 1000" in the end
> I get the following:
> Limit (cost=0.00..75.33 rows=100 width=12)
> -> Index Scan using tmp1_idx on tmp1 (cost=0.00..2259857.96 rows=300
> width=12)
>
> If I increase limit to 70 index is not used again and the difference in
> execution time is very noticeable:
> 1 millisecond for "limit 60"; and 6 seconds for "limit 70"
>
> Is there anything what can be configured to make it use the index for the
> ordering?
>
> Best Regards,
> Alexei Novakov.
The planner cannot estimate the speed of your disks and thinks, that
doing a seqscan will be faster for so many columns, then scanning the
index, and then do a random lookup on the table.
Have a look on the documentation for the following planner
configuration parameters: effective_cache_size, seq_page_cost and
random_page_cost
With best regards,
-- Valentine Gogichashvili
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4763: postgres service unstable, even during install
On May 8, 9:11 am, Kevin Field wrote: > On May 4, 2:10 pm, dp...@pgadmin.org (Dave Page) wrote: > > > The installer is still looking for perl58.dll, whilst the server > > needs perl510.dll. I've committed a fix for that (and the other PLs > > which were similarly afflicted). > > > I've rebuilt the installer (using the existing binaries from the last > > build) and uploaded it tohttp://developer.pgadmin.org/~dpage/. Please > > give it a whirl. > > Thanks for the rebuild. I finally got to try it today, but it was the > same story (at least, without renaming folders and such): can't > install plperl, then can't even find the service running to try > plperlu; after the install, I start the service again and run my > script and at the point at which it would run pg_restore, that fails > and the service is no longer running. The new 8.4-beta2 seems to be behaving the exact same way. :( -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4763: postgres service unstable, even during install
On May 22, 9:28 am, Kevin Field wrote: > On May 8, 9:11 am, Kevin Field wrote: > > > > > On May 4, 2:10 pm, dp...@pgadmin.org (Dave Page) wrote: > > > > The installer is still looking for perl58.dll, whilst the server > > > needs perl510.dll. I've committed a fix for that (and the other PLs > > > which were similarly afflicted). > > > > I've rebuilt the installer (using the existing binaries from the last > > > build) and uploaded it tohttp://developer.pgadmin.org/~dpage/. Please > > > give it a whirl. > > > Thanks for the rebuild. I finally got to try it today, but it was the > > same story (at least, without renaming folders and such): can't > > install plperl, then can't even find the service running to try > > plperlu; after the install, I start the service again and run my > > script and at the point at which it would run pg_restore, that fails > > and the service is no longer running. > > The new 8.4-beta2 seems to be behaving the exact same way. :( I tried again just now, renaming my Perl 5.8 directory before installing so it wouldn't find it, but it still behaved the exact same way. Then I tried running 'create language plperl' from pgAdmin3 and all it said was ' ERROR *' and shut down the entire PostgreSQL service. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4763: postgres service unstable, even during install
On Fri, May 22, 2009 at 9:28 AM, Kevin Field wrote: > On May 8, 9:11 am, Kevin Field wrote: >> On May 4, 2:10 pm, dp...@pgadmin.org (Dave Page) wrote: >> >> > The installer is still looking for perl58.dll, whilst the server >> > needs perl510.dll. I've committed a fix for that (and the other PLs >> > which were similarly afflicted). >> >> > I've rebuilt the installer (using the existing binaries from the last >> > build) and uploaded it tohttp://developer.pgadmin.org/~dpage/. Please >> > give it a whirl. >> >> Thanks for the rebuild. I finally got to try it today, but it was the >> same story (at least, without renaming folders and such): can't >> install plperl, then can't even find the service running to try >> plperlu; after the install, I start the service again and run my >> script and at the point at which it would run pg_restore, that fails >> and the service is no longer running. > > The new 8.4-beta2 seems to be behaving the exact same way. :( We know :-(. The problem seems to occur deep inside perl. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] DDL triggers
In an attempt to throw the authorities off his trail, pete...@gmx.net (Peter Eisentraut) transmitted: > On Thursday 21 May 2009 10:52:33 ЦелÑйко ÐмиÑÑий wrote: >> When DDL triggers will be supported by PostgreSQL? > > There are currently no concrete plans for that, but if someone comes up with > a > good design and implementation, it could be acceptable. But don't hold your > breath. An API that provides access to the ALTER statement would be a mighty good thing. I'd suppose that something like NEW_STATEMENT would be nice to have access to. -- let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];; http://linuxdatabases.info/info/internet.html Why do they sterilize needles for lethal injections? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4821: LIKE '%_' fails
The following bug has been logged online: Bug reference: 4821 Logged by: Andrew Gierth Email address: and...@tao11.riddles.org.uk PostgreSQL version: 8.3-8.4 Operating system: all Description:LIKE '%_' fails Details: # select 'foo' like '%_'; ?column? -- f (1 row) correct result would be 't' 8.1 and 8.2 seem to get this one right. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
