Re: [PERFORM] Slow plan for MAX/MIN or LIMIT 1?

2013-09-25 Thread Sam Wong
> -Original Message- > From: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Sam Wong > Sent: Thursday, September 26, 2013 0:34 > To: 'postgres performance list' > Subject: Re: [PERFORM] Slow

Re: [PERFORM] Slow plan for MAX/MIN or LIMIT 1?

2013-09-25 Thread Sam Wong
> -Original Message- > From: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Merlin > Moncure > Sent: Wednesday, September 25, 2013 23:55 > To: Claudio Freire > Cc: Sam Wong; postgres performance list > Subject:

Re: [PERFORM] Slow plan for MAX/MIN or LIMIT 1?

2013-09-25 Thread Sam Wong
Hi All and Merlin, So here is the explain analyze output. -- Query A -- single row output, but very slow query -- SELECT min(log_id) FROM event_log WHERE event='S-Create' AND insert_time>'2013-09-15' and insert_time<'2013-09-16' http://explain.depesz.com/s/3H5 Result (cost=134.48..134.4

[PERFORM] Slow plan for MAX/MIN or LIMIT 1?

2013-09-24 Thread Sam Wong
Hi There, I have hit a query plan issue that I believe is a bug or under-estimation, and would like to know if there it is known or if there is any workaround. This event_log table has 4 million rows. "log_id" is the primary key (bigint), there is a composite index "event_data_search" o

Re: [PERFORM] LIKE op with B-Tree Index?

2012-10-17 Thread Sam Wong
> Moncure wrote on Thursday, October 18, 2012 1:45 > On Tue, Oct 16, 2012 at 8:01 PM, Sam Wong wrote: > >> On Wednesday, October 17, 2012 4:30, Merlin Moncure wrote, > >> > >> On Tue, Oct 16, 2012 at 3:15 AM, Sam Wong wrote: > >> > Hi communities, &

Re: [PERFORM] LIKE op with B-Tree Index?

2012-10-16 Thread Sam Wong
> On Wednesday, October 17, 2012 4:30, Merlin Moncure wrote, > > On Tue, Oct 16, 2012 at 3:15 AM, Sam Wong wrote: > > Hi communities, > > > > I am investigating a performance issue involved with LIKE '%' on > > an index in a complex query with join

[PERFORM] LIKE op with B-Tree Index?

2012-10-16 Thread Sam Wong
Hi communities, I am investigating a performance issue involved with LIKE '%' on an index in a complex query with joins. The problem boils down into this simple scenario---: Scenario My database locale is C, using UTF-8 encoding. I tested this on 9.1.6 and 9. 2.1. Q1. SELECT * FROM

[PERFORM] LIKE op with B-Tree Index?

2012-10-16 Thread Sam Wong
Hi communities, I am investigating a performance issue involved with LIKE '%' on an index in a complex query with joins. The problem boils down into this simple scenario---: Scenario My database locale is C, using UTF-8 encoding. I tested this on 9.1.6 and 9. 2.1. Q1. SE

[PERFORM] Index use difference betweer LIKE, LIKE ANY?

2011-02-25 Thread Sam Wong
I found that "LIKE", "= ANY (...)", "LIKE .. OR LIKE .." against a text field used the index correctly, but not "LIKE ANY (...)". Would that be a bug? Here is my table and index: CREATE TABLE shipment_lookup ( shipment_id text NOT NULL, lookup text NOT NULL ); CREATE INDEX shipment_lookup

Re: [PERFORM] Perl Binding affects speed?

2011-02-25 Thread Sam Wong
From: Martin Kjeldsen, Sent: 2011/2/25, 20:59 > > On 25/02/2011, at 13.25, Marti Raudsepp wrote: > > > On Fri, Feb 25, 2011 at 05:02, Sam Wong wrote: > >> * But if I do this - using binding: > >> $dbh->selectall_arrayref("SELECT * from shipment_lookup

[PERFORM] Perl Binding affects speed?

2011-02-25 Thread Sam Wong
I run into performance problem when I pass the condition/variable in binding ways, however if I put them in the query string it's absolutely fine. Here is my table and index: CREATE TABLE shipment_lookup ( shipment_id text NOT NULL, lookup text NOT NULL ); CREATE INDEX shipment_lookup_pre