Re: How to read query plan

Miroslav Šulc wrote:

> John Arbash Meinel wrote:

...

> Many of the columns are just varchar(1) (because of the migration from
> MySQL enum field type) so the record is not so long as it could seem.
> These fields are just switches (Y(es) or N(o)). The problem is users
> can define their own templates and in different scenarios there might
> be displayed different information so reducing the number of fields
> would mean in some cases it wouldn't work as expected. But if we
> couldn't speed the query up, we will try to improve it other way.
> Is there any serious reason not to use so much fields except memory
> usage? It seems to me that it shouldn't have a great impact on the
> speed in this case.

Is there a reason to use varchar(1) instead of char(1). There probably
is 0 performance difference, I'm just curious.

>
>> Have you thought about using a cursor instead of using limit + offset?
>> This may not help the overall time, but it might let you split up when
>> the time is spent.
>> ......
>
>
> No. I come from MySQL world where these things are not common (at
> least when using MyISAM databases). The other reason (if I understand
> it well) is that the retrieval of the packages of 30 records is not
> sequential. Our app is web based and we use paging. User can select
> page 1 and then page 10, then go backward to page 9 etc.
>
Well, with cursors you can also do "FETCH ABSOLUTE 1 FROM
<cursor_name>", which sets the cursor position, and then you can "FETCH
FORWARD 30".
I honestly don't know how the performance will be, but it is something
that you could try.

>> And if I understand correctly, you consider all of these to be outer
>> joins. Meaning you want *all* of AdDevicesSites, and whatever info goes
>> along with it, but there are no restrictions as to what rows you want.
>> You want everything you can get.
>>
>> Do you actually need *everything*? You mention only needing 30, what
>> for?
>
>
> For display of single page consisting of 30 rows. The reason I query
> all rows is that this is one of the filters users can use. User can
> display just bigboards or billboards (or specify more advanced
> filters) but he/she can also display AdDevices without any filter
> (page by page). Before I select the 30 row, I need to order them by a
> key and after that select the records, so this is also the reason why
> to ask for all rows. The key for sorting might be different for each run.
>
How are you caching the information in the background in order to
support paging? Since you aren't using limit/offset, and you don't seem
to be creating a temporary table, I assume you have a layer inbetween
the web server and the database (or possibly inside the webserver) which
keeps track of current session information. Is that true?

> These might be the other steps in case we cannot speed-up the query. I
> would prefer to speed the query up :-)

Naturally fast query comes first. I just have the feeling it is either a
postgres configuration problem, or an intrinsic problem to postgres.
Given your constraints, there's not much that we can change about the
query itself.

> In fact, on MySQL I didn't see any slow reactions so I didn't measure
> and inspect it. But I can try it if I figure out how to copy the
> database from PostgreSQL to MySQL.

I figured you still had a copy of the MySQL around to compare to. You
probably don't need to spend too much time on it yet.

John
=:->